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.
Divinedar0923Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zorvek (Kevin Jones)ConsultantCommented:
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
Divinedar0923Author Commented:
Okay either way I do it I'm coming up with an error. See the attached.
Divinedar0923Author Commented:
Heres the attachment.
cross-reference.doc
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

zorvek (Kevin Jones)ConsultantCommented:
Something seems fishy. Why are you getting a circular reference? Why are you inserting the formula ON the last sheet? Seems sort of an elaborate and unnecessary way to just put in =Q6+7.

Now, if you are trying to get the date from the PREVIOUS worksheet, then we need a different solution. Below is a UDF that returns a cell value from a relative worksheet. In your case set the formula to:

   =IndirectRelativeWorksheet(Q6, -1)+7

Add the above code to your template and use this code to create the new tab:

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

The UDF code follows below. Place this code in any general code module.

[Begin Code Segment]

Public Function IndirectRelativeWorksheet( _
      ByVal Range As Range, _
      ByVal Offset As Long _
   ) As Range

' Return the cell value from the worksheet offset before or after the worksheet
' in which this function is called.
'
' Syntax
'
' IndirectRelativeWorksheet(Range, Offset)
'
' Range - Any cell reference. The reference is use to determine which cells to
'   return on the found worksheet.
'
' Offset - The number of worksheets offset from the worksheet containing the
' formula. If the resulting offset worksheet is before the first worksheet or
' after the last worksheet the first or last  worksheet is used.
   
   Dim Worksheet As Worksheet
   Dim Index As Long
   
   ' The following line should be uncommented if the UDF does not calculate when needed
   'Application.Volatile
   
   Set Worksheet = Application.Caller.Parent
   Index = Application.Min(Application.Max(Worksheet.Index + Offset, 1), ThisWorkbook.Worksheets.Count)
   Set IndirectRelativeWorksheet = ThisWorkbook.Worksheets(Index).Range(Range.Address)

End Function

[End Code Segment]

Kevin

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Divinedar0923Author Commented:
You are so totally awesome. That works perfectly.
Divinedar0923Author Commented:
You are so so so so so so totally awesome. Just what I needed
Divinedar0923Author Commented:
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
Divinedar0923Author Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.