Automatic reference to a worksheet

New Contributor

Hi everyone. My knowledge on Excel is quite poor, so forgive me if my question is dumb.

What I need to do is to extract data from another workbook, and at a determined cell from an specific worksheet. The problem is that I want to get the name of the Worksheet from the text of another cell.

For instance, on cell B2 I want to copy the value of cell C2 of sheet "Sheet2" of workbook "Workbook2", so I put this formula on cell B2:

='[Workbook2]Sheet2!C$2'

No problem with that. But now I want to automate the procedure, and I want to copy (on cell B3, B4, etc.), the value of cell (C3, C4, etc.) from the same workbook, but from different sheets, and the name of the sheet is in cells (A3, A4, etc.), like in this image:

JosIrustaSolera_0-1643151035333.png

So I thought of something like:

='[Workbook2](A3)!C$3'

But of course it doesn't work. I'm sorry for the long explanation, and would be glad if someone could help (different ways to solve it are, of course, accepted).

 

Thanks in advance

José

 

2 Replies

@JosIrustaSolera 

You can use the following formula in B2:

=INDIRECT("'[Workbook2]"&A2&"'!C"&ROW())

and fill this down.

Warning: it will only work if Workbook2 is open in Excel. The INDIRECT function does not work with closed workbooks.

Thank you for your quick reply. I could quite solve it yesterday using INDIRECT, ADDRESS, CONCATENATE (I didn't know that "&" could be used for the same), and REPLACE (to fix the way ADDRESS function gives the address). Though I didn't know that the book should be opened for it to work, that's a shame... I wonder if there's a way to solve it with another function that doesn't require the other book to be opened.