Link to home
Start Free TrialLog in
Avatar of BEBaldauf
BEBaldauf

asked on

Excel 2010 INDIRECT not working

Please help, I cannot seem to get the INDIRECT formula to work...

I currently have:
=INDIRECT("'["&INDIRECT("A1")&".xlsx]"&INDIRECT("A2")&"'!"&INDIRECT("A3"))

The value in A1 is     C:\Users\me\Desktop\test-variable values
The value in A2 is     test-variable values
The value in A3 is     w1

The file being references is open, but I just get #REF!

Thank you, if you can help!!!
Avatar of Shanan212
Shanan212
Flag of Canada image

="["&INDIRECT("A1")&".xlsx]"&INDIRECT("A2")&"'!"&INDIRECT("A3")

Open in new window


Try above

What are you trying to do?
Avatar of barry houdini
You should only need a single INDIRECT function and you don't use quotes around the cell references, i.e.

=INDIRECT("'["&A1&".xlsx]"&A2&"'!"&A3)

regards, barry
Avatar of BEBaldauf
BEBaldauf

ASKER

I'm trying to simply get the contents of cell W1 out of the workbook being referenced.  (first step in a process)

Neither suggestion worked.
Shanan212,  your formula returned the string:   [C:\Users\bbaldauf\Desktop\test-variable values.xlsx]test-variable values'!w1
Barry, your formula returned #REF!

Any other suggestions?
=INDIRECT("'"&INDIRECT("A1")&"["&INDIRECT("A2")&".xlsx]"&INDIRECT("A3")&"'!"&INDIRECT("A4"))

Open in new window


This works as long as workbook is open
Still getting #REF!
ASKER CERTIFIED SOLUTION
Avatar of Shanan212
Shanan212
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
HEY!  You're awesome!  That did it!
MUCH APPRECIATED!
The best way to do this is for you to do this is probably to work out what the whole reference should be. Open the source file, and then in the formula worksheet type = in a cell then select the cell you need from the source workbook - that will give you the reference. You can then build that path with INDIRECT function

Do you know the required reference - if the file is open I doubt you need the whole path?

regards, barry
SUPER AWESOME, PATIENT, FAST, PERFECT!!!!!