# Named Range

#### praboo21

##### New Member
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.

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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?

"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 ?

You could use this formula

=INDEX(food,4)

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
ABCDE
114
224
33
44
55
Sheet1

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?

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

Thank u Very much

Replies
3
Views
150
Replies
4
Views
302
Replies
3
Views
191
Replies
1
Views
132
Replies
1
Views
478

1,196,487
Messages
6,015,502
Members
441,898
Latest member
kofafa

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back