Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Updating Indirect Reference(s)
I have a workbook with indirect references for vlookups into other workbooks.
The indirect reference is not recognized as a link when I open the spreadsheet and does not update. I must open the referenced spreadsheets for the vlookup to work. This is monthly budget data, so by December I'll be opening 72 workbooks (and Enabling macros for each one). Any way to have the indirect reference be seen as a link? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Updating Indirect Reference(s)
Sunrise TG <Sunrise wrote
I have a workbook with indirect references for vlookups into other workbooks. The indirect reference is not recognized as a link when I open the spreadsheet and does not update. I must open the referenced spreadsheets for the vlookup to work. This is monthly budget data, so by December I'll be opening 72 workbooks (and Enabling macros for each one). Any way to have the indirect reference be seen as a link? If you mean you're using the INDIRECT function to refer to ranges in other workbooks, then there's no way to make them work unless *ALL* the other files are open. Why are you using indirect links? It may be easier to use, er, direct external reference links which wouldn't require that the other workbooks be open. Give a few examples of these formulas. There may be workarounds. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Updating Indirect Reference(s)
Excel's INDIRECT function must have the referenced workbook open.
Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ That includes =INDIRECT.EXT function which does not require the workbook to be open. Gord Dibben MS Excel MVP On Wed, 7 Feb 2007 12:59:02 -0800, Sunrise TG <Sunrise wrote: I have a workbook with indirect references for vlookups into other workbooks. The indirect reference is not recognized as a link when I open the spreadsheet and does not update. I must open the referenced spreadsheets for the vlookup to work. This is monthly budget data, so by December I'll be opening 72 workbooks (and Enabling macros for each one). Any way to have the indirect reference be seen as a link? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Updating Indirect Reference(s)
I'll include an example, maybe someone has a better way. I didn't use direct
reference because I'm looking up the same actual vs. budget data from 8 different dept. workbooks with similar naming conventions (dept name in filename), changing each month by the date referenced in the file names. I think you'll see by the indirect reference. I built the reference from several cells that contain common and variable info. In total there are 144 similar but different indirect references on each monthly tab. =VLOOKUP(B6,INDIRECT(J4),3,FALSE) where B6 is a known subtotal row header and J4 contains: 'F:\Financials\2007\1-Jan\Dept_Income_Statements\[215_TG_DEPT_2007-01-31.xls]Sheet1'!$B$2:$AA$100 My only work around so far is to keep a clean template tab and after the data is loaded each month, copy/paste values (for historical reference). Then I just need to open the 8 current month workbooks. Alternately, I can just enter the data each month, but that's not as much fun! Thanks! "Harlan Grove" wrote: Sunrise TG <Sunrise wrote I have a workbook with indirect references for vlookups into other workbooks. The indirect reference is not recognized as a link when I open the spreadsheet and does not update. I must open the referenced spreadsheets for the vlookup to work. This is monthly budget data, so by December I'll be opening 72 workbooks (and Enabling macros for each one). Any way to have the indirect reference be seen as a link? If you mean you're using the INDIRECT function to refer to ranges in other workbooks, then there's no way to make them work unless *ALL* the other files are open. Why are you using indirect links? It may be easier to use, er, direct external reference links which wouldn't require that the other workbooks be open. Give a few examples of these formulas. There may be workarounds. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
External cell references using INDIRECT & ADDRESS | Excel Worksheet Functions | |||
Can you use INDIRECT in 3-D references? | Excel Worksheet Functions | |||
Trick? for using INDIRECT references in Data Source of Chart? | Charts and Charting in Excel | |||
Using Indirect en direct cell references | Excel Discussion (Misc queries) | |||
Indirect references in a linked formula | Excel Worksheet Functions |