Link to home
Start Free TrialLog in
Avatar of Divinedar0923
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").Visible = True
    Sheets("Template").Select
    Sheets("Template").Copy before:=Sheets("Misc")
    Worksheets("Template (2)").Visible = True
    Worksheets("Template").Visible = 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.
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

Use this code:

Function copy_template()
    Dim Index As Long
    Dim NewWorksheet As Worksheet
    Worksheets("Template").Visible = True
    Sheets("Template").Select
    Sheets("Template").Copy before:=Sheets("Misc")
    Worksheets("Template (2)").Visible = True
    Worksheets("Template").Visible = 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
    ThisWorkbook.Names.Add "LastWorksheetName", "=""" & NewWorksheet.Name & """"
End Function

And change your formulas to:

=INDIRECT("'" & LastWorksheetName & "'!Q6")+7

Kevin
Avatar of Divinedar0923
Divinedar0923

ASKER

Okay either way I do it I'm coming up with an error. See the attached.
Heres the attachment.
cross-reference.doc
ASKER CERTIFIED SOLUTION
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You are so totally awesome. That works perfectly.
You are so so so so so so totally awesome. Just what I needed
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
Thank you I figured it out.
=IF(IndirectRelativeWorksheet(Q13, -1)<>0,(IndirectRelativeWorksheet(Q13, -1)+IndirectRelativeWorksheet(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.