Prev: Combine rows to print on one label
Next: Changing dates across multiple worksheets
From: vickya on 25 May 2010 11:30
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
From: Dave Peterson on 25 May 2010 12:59
=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.



vickya wrote:
>
> 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
 | 
Pages: 1
Prev: Combine rows to print on one label
Next: Changing dates across multiple worksheets