Indirect function across other workbook with multiple sheets

[g3org]

Hello to everyone,
I am trying to import a range of cells from one workbook to another and i am facing some issues.
I have two workbooks with around 500 sheets each one and I want the range of cells A1:K38 from Book1 to be transferred to Book2. Each Workbooks has the sheets with the same name and in each sheet i have one cell that contain the name of the sheet. Any suggestion is well received.

Thank you in advance!

 

[6StringJazzer]

Is this is one-time copy, or do you need it to be dynamic, i.e., Book2 must be constantly kept up to date as Book1 changes?

Which cell contains the sheet name?

This can be done with INDIRECT but Book1 will have to have to be open. If it is not open, the formulas with INDIRECT will return a #REF error.

As an example, if the sheet name is in cell L1 you can put this formula in cell A1 and fill down and to the right to K38:

=INDIRECT("[Book1.xlsx]"&$L$1&"!"&CELL("address",A1))

With 500 sheets I suspect you are going to need to use VBA.

 

[g3org]

You are great, many thanks! For me works perfect.