Named Range

[praboo21]

I have a range with integers 1-5 in A1:A5.I have named the range as 'Food'

Why is it when the formula “=food” is placed in the cell B1,I am getting 1 and the cell C2 I am getting 2.What is the explanation for it.

How should I refer to a value in the range Food in another cell?

No VBA is involved.Just plain excel.

 

[Barry Katcher]

That formula, if placed in any cell in rows 1 thru 5, will give you the value from the corresponding row in your FOOD range. If placed in any other rows, it will give you a #VALUE! error.

What exactly are you trying to return in the cells with the formula? Can you give us an example of what you expect in, say, cell B1 and C2?

 

[praboo21]

"That formula, if placed in any cell in rows 1 thru 5, will give you the value from the corresponding row in your FOOD range. If placed in any other rows, it will give you a #VALUE! error. "

Hi could you pls provide an explanation why that happens?I am a college student quite new to excel definitions.

How should I refer to a value in the range Food in another cell? --> Say I wanna display the value of the 4th value in the range Food in cell E12.How should I do that ?

 

[barry houdini]

You could use this formula

=INDEX(food,4)

 

[Barry Katcher]

If you simply want to link E12 to the 4th value in range A1:A5, just use =A4.

Or, you could name each cell in A1:A5 as Food1, Food2, etc, etc. and use =Food4 in cell E12.

I suspect, though, that you haven't fully explained what you are looking for in linkage.

Book1
	A	B	C	D	E
1	1				4
2	2				4
3	3
4	4
5	5
Sheet1

 

[praboo21]

I have named the range A1:A5 as Food.

I would like to refer to the value in A3 in cell say E13 using the range Food.

What formula can I use?

 

[Barry Katcher]

If you absotively, posilutely must use a reference to the range name "Food" in your formula, then use Barry H's formula above.

 

[praboo21]

Thank u Very much