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 |
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 |
All times are GMT +1. The time now is 06:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com