Referening cells inside a named range

**kayard** · 06-15-2010, 04:57 AM

Hi to everyone.

I have a named range in cells A1:E1. Let's call it named_range.
If I enter in cell B2 the formula =named_range I get the value un cell B1. Good.
I would like to enter a formula in cell B2 that gives the value in cell A1 (or C1 for that matter). The formula must use the name of the range to improve readability.

I was thinking something like =OFFSET(named_range;;-1) to get the value of A1 when entered in B2 but it doesn't work.

Any help ?

Thanks a lot for your support

Paolo

Italy

**sweep** · 06-15-2010, 05:00 AM

Hi,

Try =INDEX(named_range,1)

for the 1st member of the named range

**kayard** · 06-15-2010, 05:16 AM

yes, but that will always give me the first cell of the named range. I need something relative in the sense that it should give me the cell on the left of where i put the formula. If i put it in c2 it must give me b1, if put in d2 it has to give c1 and so on. That's why i thought of the offset formula instead of index.

The beauty of simply writing =named_range gives is that it immedeatly returns the intersection of the collumn you enter the formula and the named range. I basically need the intersection of the collumn on the left (o on the right) and the named range.

Thanks

Paolo