0

I am trying to use a formula that would let me copy and paste it down so I dont have to link every-time

The source workbook has tabs named 1,2,3,4,5.....100

I want to use simple formula ='[test.xlsx]1'!$O$117

but I want to replace ='[test.xlsx]**A1**'!$O$117

so it is dynamic and I can copy it down

='[test.xlsx]B1'!$O$117; where B1 = 2
='[test.xlsx]C1'!$O$117; where C1 = 3

I tried ='[SCD PCAP 2Q15 - waterfall.xlsx]&1&'!$O$117

any ideas?

EnriMR
  • 3,924
  • 5
  • 39
  • 59
The B
  • 45
  • 1
  • 9

2 Answers2

0

You can do so by Two step process.

Step 1 (Thanks to @Excel Hero for this answer)

1
Open the Name Manager. Control-F3 from the worksheet, and then click the New button.

2
For the Name field in the dialog, enter EVALXFD. I just picked this name it is using column XFD in it. But you can pick whatever name you like.

3
For the Refers to field, enter this

=EVALUATE($XFD1)

4
Click OK and then Close.

Step 2

Paste below mentioned formula in last helper column XFD (I am assuming you are not using this column) 

=CONCATENATE("='[Test.xlsx]",$A1,"'!$A$1")

In B1 enter this formula and drag it down:

=EVALXFD

That's it.

enter image description here

Community
  • 1
  • 1
ManishChristian
  • 3,759
  • 3
  • 22
  • 50
0

Use the INDIRECT() function. E.g., with $A$1 = 2, $A$2 = 3, ..., you can use 

=INDIRECT("'[test.xlsx]"&B1&"'!$O$117")

which Excel will concatenate =INDIRECT('[test.xlsx]2'!$O$117) and then evaluate.

Note also that I've written the references above based on copying the formula down column B (with references in column A), so the row number will change, but the reference will always be to column A.

Brendan
  • 3,901
  • 15
  • 23