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

[nsmattox]

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.

 

[Sammi]

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]

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]

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

 

[nsmattox]

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