Excel changes Sheet1! to Book1!

[lezawang]

Hi
I am using vlookup() and the table where to look for is in another sheet. I highlighted all the table including labels and named it Mytable1, so my vlookup function looked like this:

Vlookup(A2,Sheet2!Mytable1,2,false)

But to my surprised excel changed Sheet2 to the name of the file, Book1

Vlookup(A2,Book1!Mytable1,2,false)

Why is that? Also can I make Mytable1 global so excel understand it without typing Sheet1! or Sheet2! etc.?

Thank you

 

[shg]

A range name (or any other named formula) can have either worksheet scope or workbook scope. Your table has workbook scope.

If you use Name Manager, you have the option to select when the name is defined.

If you are just entering the name in the Names box, then preface the name with the sheet name if you want worksheet scope: Sheet2!Mytable