formula to look up ref in one sheet and offset in another

[vickya]

Hi there

I am trying to create a formula which will use a product code typed into a
cell in the same sheet, look up this code in a separate file, and give me the
sales figure which is offset from this code.

I.e. (if this helps)
This is the formula I have at the moment just using the other file:
=OFFSET('[Budget Tracker Master Copy 2010.xlsm]Total by Product'!$EJ$5,739,3)
but I want the code which is in EJ5 to come from my current sheet which is
in cell B8.
The range to look up in the Budget Tracker sheet would be $B$5:$MS$5

Hope this makes sense! Thanks

 

[Dave Peterson]

=offset() is one of those excel functions that won't work if the sending
workbook is closed.

If the sending workbook is open, then you could use:

=offset(indirect
("'[Budget Tracker Master Copy 2010.xlsm]Total by Product'!"&B8),739,3)

This expression will break twice if you close the sending workbook. =indirect()
is another function that won't work if the sending workbook is closed.

Hi there

I am trying to create a formula which will use a product code typed into a
cell in the same sheet, look up this code in a separate file, and give me the
sales figure which is offset from this code.

I.e. (if this helps)
This is the formula I have at the moment just using the other file:
=OFFSET('[Budget Tracker Master Copy 2010.xlsm]Total by Product'!$EJ$5,739,3)
but I want the code which is in EJ5 to come from my current sheet which is
in cell B8.
The range to look up in the Budget Tracker sheet would be $B$5:$MS$5

Hope this makes sense! Thanks