Hello, does anyone know if it is possible to use the “indirect” function with a workbook that is closed. I feel like I’m close… I can get them to work independently but not together… reason being is I’d like the workbook name to be dynamic using a separate spreadsheet to pull data from a closed workbook to prevent from having to find & replace cells.
Detail… On a separate spreadsheet I have 3 reference cells A2, B2 and C2… header for A2 is “Workbook”, header for B2 is “Sheet”, and the header for C2 is “Cell Reference” (in A2 I enter the Workbook with the xlsx extension, in B2 I enter the Sheet Name and in C3 I enter the Cell Reference)… if I use the formula…INDIRECT(‘“[“&$A$2&”]”&$B$2&’”!”&C2),””) I get the correct result as long as I have the workbook being referenced open, the minute I close the workbook the reference cells are referring to the data disappears. I’m trying to index the path and combine the dynamic function of the indirect function.
Is this possible?
Detail… On a separate spreadsheet I have 3 reference cells A2, B2 and C2… header for A2 is “Workbook”, header for B2 is “Sheet”, and the header for C2 is “Cell Reference” (in A2 I enter the Workbook with the xlsx extension, in B2 I enter the Sheet Name and in C3 I enter the Cell Reference)… if I use the formula…INDIRECT(‘“[“&$A$2&”]”&$B$2&’”!”&C2),””) I get the correct result as long as I have the workbook being referenced open, the minute I close the workbook the reference cells are referring to the data disappears. I’m trying to index the path and combine the dynamic function of the indirect function.
Is this possible?
