Feb 22 2022 02:51 PM
I have a cell in my main workbook sheet that references and pulls information in from a cell on a different sheet within the workbook. Let's say my additional sheet is called simply "2" (or it could be called the default name "Sheet2"; doesn't matter). So I may state the reference as "='2'!$D$7" to link the information in from cell D7 on my sheet called "2" into my main workbook sheet. I also have many additional sheets formatted the same way as "2", and they are called "3", "4", etc. How can I efficiently create additional incremental references on my main workbook sheet, such as "='3'!$D$7", "='4'!$D$7", etc.?? Right now I'm stuck with simply copying the sheet "2" references and then editing the references all manually and tediously (by changing the "2", to "3", "4", etc.) I cannot figure out a way to do this efficiently, including using a macro to help. Any solutions??
Feb 22 2022 03:04 PM
SolutionCreate a list of the sheet names in a range of cells, e.g. in A2, A3, A4 etc.
If the sheets are really named Sheet2, Sheet3 etc. you can do this by entering Sheet2 in A2 and then filling down.
In B2, enter the formula
=INDIRECT("'"&A2&"'!D7")
Fill down.
Feb 22 2022 03:44 PM
@Hans Vogelaar That seems to be working. Thank you!
Feb 22 2022 03:04 PM
SolutionCreate a list of the sheet names in a range of cells, e.g. in A2, A3, A4 etc.
If the sheets are really named Sheet2, Sheet3 etc. you can do this by entering Sheet2 in A2 and then filling down.
In B2, enter the formula
=INDIRECT("'"&A2&"'!D7")
Fill down.