# How to get formulas to reference a sheet that has not been created yet

#### nsmattox

##### New Member
I'm creating a "scorecard" that will generate a sheet of questions based on an input tab - for that newly created tab to score correctly, how do I write that in my formula

I'm using sumif, index match match to find the areas I need to have scored, but what do I use to make it reference the newly created sheet?

The sheet will be named differently each time, so is there a generic way to make it always look to that newly created sheet?

I hope I described that correctly. Any insight would be really helpful.

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### Sammi

##### New Member
If you had a table on the new sheet and knew what that was going to be called you could reference that. For example, I might know that a table on the new sheet will be called Ox where x references a number that I have in cell A2. I could then combine a few formulas to reference that tab.

=RIGHT(CELL("filename",INDIRECT("O"&\$A\$2)),LEN(CELL("filename",INDIRECT("O"&\$A\$2)))-FIND("]",CELL("filename",INDIRECT("O"&\$A\$2))))

Sorry there’s quite a bit going on in this formula but it does work. Let me know if you need anything explained.

#### nsmattox

##### New Member
So if my tab name is always going to be generated from A5, and it's always going to be a name - Like HCMC or SLMC or whatever, how would i write that into the formula?

#### Sammi

##### New Member
That's significantly easier. =INDIRECT("'"&A5&"'!A:A")
If A5 was HCMC that'd reference 'HCMC'!A:A

#### nsmattox

##### New Member
Sammi - thanks so much - that is a HUGE help - another quick question - I'm trying to get an input sheet to create a scorecard, based on blocks of questions they can check or uncheck - the code i currently have pastes the correct information into my new worksheet, but it pastes just the values and I need the formulas to transition over for it to work properly, I would also like it to autosize if possible - here is my code - any idea on how to get that to work?

Sub ComboNEW()
Sheets("Master Question List").Select
Range("B1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'Lookup TRUE in Column A then Copy/Paste to Scorecard
Application.CutCopyMode = False

Const Source As String = "Master Question List"
Const Destination As String = "Scorecard"
Const ColumnsToSearch As String = "A:A"
Const DataStartRow As Long = 2
Application.ScreenUpdating = False
With Worksheets(Source)
Intersect(.Rows("1:" & DataStartRow - 1), .Range(ColumnsToSearch)). _
EntireRow.Copy Worksheets(Destination).Range("A1")
With .Range(ColumnsToSearch)
.Replace "TRUE", "#N/A", xlWhole
With .SpecialCells(xlCellTypeFormulas)
.Value = "TRUE"
.EntireRow.Copy Worksheets(Destination).Cells(DataStartRow, "A")
End With
Worksheets(Destination).Range(ColumnsToSearch).Columns.AutoFit
End With

Replies
0
Views
55
Replies
2
Views
59
Replies
7
Views
211
Replies
5
Views
54
Replies
2
Views
101

1,127,623
Messages
5,625,940
Members
416,143
Latest member
JoyceMB

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back