Excel Dynamic Formula/Reference Workbook

0
votes

I'm trying to create a dynamic reference within a formula; meaning the referenced workbook and subsequent sheet name can be changed and the appropriate data loaded.

enter image description here

I'm aware that a static formula reference could be done as follows:

=SUM('C:\Reports\[SOTDJ17.xlsx]Summary'!C10:C25)

but I want to be able to change the workbook name, and it will reference information from that particular workbook and change the data. The workbook name is stored in B5 and the sheet is stored in B8. I thought I might be able to use something simple like:

=SUM('C:\Reports\[B5]B8'!C10:C25)

but it doesn't seem to work. Is there a syntax error or something else I am missing?

(I should note that I am trying to reference a closed workbook)

1
excelvbaexcel-formula
Welcome to Stack Overflow! Unfortunately, this question has nothing to do with programming, and is thus off-topic for this site. – Joe C
@Joe C - The excel-formula tag states "This tag is for Microsoft Excel questions where the question or answers are specifically about the structure, syntax, or output of an Excel formula." I disagree with your assertion that my question is off-topic. – J Speight

1 Answers

0
votes

You'll want to use Indirect():

=SUM(INDIRECT("["&B5&"]"&B8&"!$C$10:$C$25"))

Note that the workbook you're referring to must be open.