0

I want to copy the value of one Cell from another workbook. The file path of the other workbook is generated by a value of the first workbook. 

='folderpath\[11111.xlsm]Checklist'!$CE$29

11111 should be a dynamic generated value and exists for example in Cell A1.

If I change the value 11111 to 22222, the formula should adapt to access the value of the Cell in the worksheet "22222.xlsm"

Community
  • 1
  • 1
Heinrich
  • 11
  • 3
  • Look into [`INDIRECT`](http://www.contextures.com/xlFunctions05.html) – ashleedawg Apr 18 '18 at 12:31
  • Possible duplicate of [How to turn a string formula into a "real" formula](https://stackoverflow.com/questions/4471884/how-to-turn-a-string-formula-into-a-real-formula) – Raunak Thomas Apr 18 '18 at 12:34

1 Answers1

0

You can use 

=INDIRECT("folderpath\[" & A1 & ".xlsm]Checklist!$CE$29")

This works if your excel "speaks" is in english. If you have another language you have to translate it. you can find some examples here

enter image description here

Matthias Neubert
  • 275
  • 1
  • 5
  • 24
  • @Heinrich Remember to use INDIRECT in your own language your excel "speaks" I have edited my answer and added a link with an excel translator – Matthias Neubert Apr 18 '18 at 13:04
  • 1
    @Heinrich To use `INDIRECT` to reference another workbook, that workbook must be Open, or you will get a `#REF!` error – Chronocidal Apr 18 '18 at 13:22
  • I used Indirect in my language. Even when the other workbook is open, it still does not work. They main reason for using this, was not needing to open the other workbook. So VBA code will be the better way. – Heinrich Apr 18 '18 at 13:30