INDIRECT reference

[scottleger4]

Can't quite figure out what I have wrong here...

=INDIRECT("'[O:\OPS\Wdrive\public\Mike Delclos\DE Histories SC\Scott 05_06_34.xlsx]"&B$58&"'!$I1973")

...is the formula I am trying to use in place of...

='[Scott 05_06_34.xlsx]ABQ'!$I1973

The "ABQ" part is what I am trying to turn into a variable. I'm sure it's something simple, just can't quite get it right. Thanks in advance.

 

[RoryA]

You can't use INDIRECT with a closed workbook, so you don't need the path. It's just:

=INDIRECT("'[Scott 05_06_34.xlsx]"&B$58&"'!$I1973")

 

[scottleger4]

hmm...is there a different way to get to that same data when the workbook is closed? The data I need to pinpoint is in a range where each row is a different date and each column is a different location. Maybe an index formula?

 

[RoryA]

INDEX won't work if the sheet name needs to be a variable. You'd need code such as Harlan Grove's Pull function.

 

[scottleger4]

Sounds like there isn't a way to grab data from a closed workbook with a variable sheet name without some extra special code. Excel just isn't capable of doing it on it's own?

 

[RoryA]

Nope.