Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can offset be used in this formula? | Excel Discussion (Misc queries) | |||
Copy link to offset sheet | Excel Worksheet Functions | |||
Compare Cell Values, Offset(-1,0), Offset(-1,-1), and xlFillDefaul | Excel Worksheet Functions | |||
Offset in another sheet | Excel Discussion (Misc queries) | |||
Using offset more than once on the same sheet | Excel Worksheet Functions |