Jan 25 2022 02:54 PM
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:
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é
Jan 25 2022 03:08 PM
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.
Jan 26 2022 09:38 AM