CODEWITHSUNDEEP
excelparametersexcel-formulafilepath
Mandelbrot1987
Mandelbrot1987

Reputation: 11

Dynamically referencing a cell

I would like to set up Names for the drive, file path, Excel spreadsheet name and sheet name in cells in Excel, and then concatenate them together when referencing external cells. This will allow me to create macros that reference sheets dynamically in the sense that it does not matter where the spreadsheet is.

So far, I have the following:

Drive = C
Location = Users\ashleys\Desktop\New Quote Production\Quote Location 3\Ash
Name = Book3
Sheet = Sheet1

(Please note that the = sign above can be read as "assigned Name as")

I concatenate these together to create another Name (called Final) in the following fashion:

Final = "'"&Drive&":\"&Location&"["&Name&".xlsx]"&Sheet&"'!"

Final resolves to be the following:

C:\Users\ashleys\Desktop\New Quote Production\Quote Location 3\Ash\[Book3.xlsx]Sheet1'!

So, as far as I am concerned, so far so good!

However, I am embarrassingly stumbling at the last hurdle. when I use the Final defined Name in cell references, it does not resolve in the way I expect. So, if I am in Book1 and I use the code above to refer to cell A3 (which for example contains the word "Success!") in Book3, I would hope that typing the following into a cell in Book1 would resolve to "Success!": =Full&A3

Instead, the A3 resolves to A3 in Book1.

Upvotes: 1

Views: 134

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19857

You'd need to use INDIRECT for that to work, but the other workbook would have to be open.

http://www.cpearson.com/excel/indirect.htm

As the workbook has to be open for it to work there's no need to add the file location to the string. It may be useful in some VBA code used in the Workbook open event to automatically open the other workbook though, or look at Excel workspaces to open the other files.

http://www.ozgrid.com/Excel/save-as-workspace.htm

i.e. =INDIRECT("'[Book3.xlsx]Sheet1'!$A$1") or =INDIRECT(Final) if Final is a named value.

Upvotes: 1

Related Questions