If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
formula to look up ref in one sheet and offset in another
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 |
#2
|
|||
|
|||
formula to look up ref in one sheet and offset in another
=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 |
Thread Tools | |
Display Modes | |
|
|