Divinedar0923
asked on
Add Tab Name to Formula
Okay I have what maybe a simple question and then again maybe not.
I have this formula in cell Q6 of every worksheet:
='Week Three'!Q6+7
"Week Three" is the tab name of the sheet before "Week Four".
How can I put this information in that cell when a new sheet is inserted. Can i add a line of code in the code below to do this? Below is my insertion code:
Function copy_template()
Dim Index As Long
Dim NewWorksheet As Worksheet
Worksheets("Template").Vis ible = True
Sheets("Template").Select
Sheets("Template").Copy before:=Sheets("Misc")
Worksheets("Template (2)").Visible = True
Worksheets("Template").Vis ible = False
Set NewWorksheet = Worksheets("Template (2)")
On Error Resume Next
For Index = 1 To 1000
Err.Clear
NewWorksheet.Name = "Week " & GetSpelledNumber(Index)
If Err.Number = 0 Then Exit For
Next Index
End Function
So in other words with I'm insert the template and it reads "Week Five" on the tab, the in cell Q6 should read ='Week Four'!Q6+7. This will update the fields for each date needed.
I have this formula in cell Q6 of every worksheet:
='Week Three'!Q6+7
"Week Three" is the tab name of the sheet before "Week Four".
How can I put this information in that cell when a new sheet is inserted. Can i add a line of code in the code below to do this? Below is my insertion code:
Function copy_template()
Dim Index As Long
Dim NewWorksheet As Worksheet
Worksheets("Template").Vis
Sheets("Template").Select
Sheets("Template").Copy before:=Sheets("Misc")
Worksheets("Template (2)").Visible = True
Worksheets("Template").Vis
Set NewWorksheet = Worksheets("Template (2)")
On Error Resume Next
For Index = 1 To 1000
Err.Clear
NewWorksheet.Name = "Week " & GetSpelledNumber(Index)
If Err.Number = 0 Then Exit For
Next Index
End Function
So in other words with I'm insert the template and it reads "Week Five" on the tab, the in cell Q6 should read ='Week Four'!Q6+7. This will update the fields for each date needed.
ASKER
Okay either way I do it I'm coming up with an error. See the attached.
ASKER
Heres the attachment.
cross-reference.doc
cross-reference.doc
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
You are so totally awesome. That works perfectly.
ASKER
You are so so so so so so totally awesome. Just what I needed
ASKER
Sorry but I have one more question for you regarding your IndirectRelativeWorksheet code. The offset counts the number of sheets you want to go back and get the information from; right? Okay I usually take codes and learn from them and that's what I'm dong with the one you sent me. But for some reason and I don't understand why. I uncommented the Application.Volatile and I still get the same errors. The calculations worked on Week Two but not on Week Three when added. See the errors in the file attached.
EXPENSE-REPORT-Revision-E.xls
EXPENSE-REPORT-Revision-E.xls
ASKER
Thank you I figured it out.
=IF(IndirectRelativeWorksh eet(Q13, -1)<>0,(IndirectRelativeWo rksheet(Q1 3, -1)+IndirectRelativeWorksh eet(T13,0) ),"")
=SUM(C13:P13) - Insert in empty column to the right and reference where I have (T13) above.
Thank you for starting me to understand.
=IF(IndirectRelativeWorksh
=SUM(C13:P13) - Insert in empty column to the right and reference where I have (T13) above.
Thank you for starting me to understand.
Function copy_template()
Dim Index As Long
Dim NewWorksheet As Worksheet
Worksheets("Template").Vis
Sheets("Template").Select
Sheets("Template").Copy before:=Sheets("Misc")
Worksheets("Template (2)").Visible = True
Worksheets("Template").Vis
Set NewWorksheet = Worksheets("Template (2)")
On Error Resume Next
For Index = 1 To 1000
Err.Clear
NewWorksheet.Name = "Week " & GetSpelledNumber(Index)
If Err.Number = 0 Then Exit For
Next Index
ThisWorkbook.Names.Add "LastWorksheetName", "=""" & NewWorksheet.Name & """"
End Function
And change your formulas to:
=INDIRECT("'" & LastWorksheetName & "'!Q6")+7
Kevin