MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Referencing names from one excel file into another


Posted by Carl Wells on August 06, 2001 8:08 AM

Hi there,
still grapelling with excel's address/name handling, mainly because I'm still a bit of a newbie :(.

Anyway, I am currently using the command

'G:\Carl\ArbitrageTemplates\[Carl MW template 1.xls]NAV calculations'!NAV

in order to read the value of the name NAV in sheet NAV calculations in workbook Carl MW template 1.xls into the current cell.

This is nasty for 2 reasons :

(i) I need to know what sheet the name is located in (this is really bad ... previously I was using

=OFFSET(INDIRECT(CELL("address",LST_CUR_PRICE)),0,0)

which worked pretty well, but I can't get this to work across files ...

(ii) I then need to be able to specify which column of the name I want to get the value from

Help!

thanks,

Carl


Posted by Joe Chin on August 06, 2001 12:35 PM

If you've named a range in another file, you can use the VLOOKUP function to specify the row and column you're looking up.

=VLOOKUP(A1,NAV,3,FALSE)

finds A1's value in the leftmost column of NAV, and then returns the value in the 3rd column.