Hey all. I'm trying to create a worksheet which copies 3 different cells over all sheets in that workbook. I know I can use INDIRECT, but that only works if I manually input that formula once for each worksheet I want to reference. I want the worksheet to automatically add or remove the data if a worksheet is added or removed.
As an example, this site shows how to actively create a list of all the workbook's sheet names:
If you add a worksheet, that new sheet is added to the list of names. If you delete one, that worksheet is removed from the list. I want to do the same thing, but with cell values instead of sheet names.
Any suggestions? Thanks!
As an example, this site shows how to actively create a list of all the workbook's sheet names:
List sheet names with formula
To list worksheets in an Excel workbook with a formula, you can use a 2-step approach: (1) define a named range called "sheetnames" with an old macro command and (2) use the TEXTAFTER function and the TRANSPOSE function to retrieve sheet names using the name. In the example shown, the formula in...
exceljet.net
If you add a worksheet, that new sheet is added to the list of names. If you delete one, that worksheet is removed from the list. I want to do the same thing, but with cell values instead of sheet names.
Any suggestions? Thanks!