I have a spreadsheet that I have created to track totals for various worksheets in a workbook. Each time I add a new worksheet to the workbook I have to set up a series of formulas on a summary page to pull information from the newly created worksheet. To do that I have to manually type my new worksheet name into the formula each time. What I want to know is there a way to use a cell reference to populate the worksheet name rather than typing it in each time.
Here is one of my formulas which is in cell B3 on my summary worksheet.
=INDEX('Sheet'!$B$11:$B$100001, COUNTA('Sheet1'!$B$11:$B$100001), 1)
However in A3 of that same sheet I have the worksheet name, Sheet1, entered. What I would like to do is use a cell reference that would translate through the formula as "Sheet1" but everything I have tried has returned a #Ref! error. For example:
=INDEX('"3"'!$B$11:$B$100001, COUNTA('"A3"'!$B$11:$B$100001), 1)
does not work. I have tried several different variations of this same thing but everything I try returns the exact same error message.
Is this doable or is it impossible?