How to create string in Excel INDIRECT function to dynamically reference cells in an external spreadsheet

Question

I can use an external reference easily in Excel 2010 like this

Cell A1 : ='BLAH.XLSX'!Fees[[#Totals],[Billable Hours]]

but lets say B1 contains FOO.XLSX

I now want A1 to be

='FOO.XLS'!Fees[[#Totals],[Billable Hours]]

I tried the indirect function

=INDIRECT(CONCATENATE("'", B1, "'!Fees[[#Totals],[Billable Hours]]"))

But this just gives a #REF error, but it resolves (I used the show steps function in excel) to:

=INDIRECT("'FOO.XLSX'!Fees[[#Totals],[Billable Hours]]")

I've tried (played with) a number of things but nothing gives me the reference correctly

suchas =INDIRECT(CONCATENATE("='", B1, "'!Fees[[#Totals],[Billable Hours]]")) which resolves to

=INDIRECT("='FOO.XLSX'!Fees[[#Totals],[Billable Hours]]")

Also I want to avoid VBA to avoid security warnings/dialogs etc., but will accept this if that's the the only way

I feel I'm really close

Indirect returns #REF if the second workbook is closed. Does it work when it's open? — Jesse
– Jesse, Commented Jan 31, 2012 at 6:44

barry houdini · Accepted Answer · 2012-01-31 13:20:46Z

1

As Jesse says, you can't use INDIRECT to refer to closed workbooks. There is no other formula solution unless you use add-in functions. INDIRECT.EXT works with closed workbooks but you need to download MOREFUNC addin to get that function, google Morefunc

answered Jan 31, 2012 at 13:20

barry houdini

46.9k8 gold badges67 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Preet Sangha Over a year ago

Thank you. Is there a VBA solution?

brettdj · Accepted Answer · 2012-02-03 10:53:47Z

1

See INDIRECT and closed workbooks at Dick's Blog

Harlan Grove's Pull function and opening the workbook directly via VBA for large scale manipulation are the two most commonly used methods

answered Feb 3, 2012 at 10:53

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

Collectives™ on Stack Overflow

How to create string in Excel INDIRECT function to dynamically reference cells in an external spreadsheet

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related