SOLVED

Incrementally creating different sheet names within a workbook on a single sheet

Copper Contributor

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??

2 Replies
best response confirmed by MarkSanDiego (Copper Contributor)
Solution

@MarkSanDiego 

Create 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.

1 best response

Accepted Solutions
best response confirmed by MarkSanDiego (Copper Contributor)
Solution

@MarkSanDiego 

Create 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.

View solution in original post