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

nsmattox

New Member
Joined
Jan 15, 2014
Messages
8
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.
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Sammi

New Member
Joined
Jan 23, 2014
Messages
27
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
Joined
Jan 15, 2014
Messages
8
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
Joined
Jan 23, 2014
Messages
27
That's significantly easier. =INDIRECT("'"&A5&"'!A:A")
If A5 was HCMC that'd reference 'HCMC'!A:A
 

nsmattox

New Member
Joined
Jan 15, 2014
Messages
8
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,605
Messages
5,625,762
Members
416,136
Latest member
senthil_sk

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top