Formula to reference "tab to the right"?

[texaschai]

Hi there,
I have a spreadsheet that contains month tabs and each month I add a new tab. Trouble is, I have some columns in the spreadsheet that contain formulas for "prior month balance". If I do a Vlookup, I have to change the formula each time I create a new month's tab since I have to change the tab name on the new month and reference the new prior month.

Is there a way to do a formula that reads like this?:

Intersection (cell) on tab to the right of current tab where row equals "name of employee" (names are in column A) meets column called "prior month vacation balance" (this column number changes each month but header description stays the same).

Thanks!
texaschai

 

[RickXL]

I can't offer you a complete solution because I am not sure that I understand exactly what you want. However, here are a couple of tools that might be useful.

The first is a pair of macros. You will probably only need one. The macros are User Defined Functions that will return the name of either the worksheet on the right (RightWS) or the worksheet on the left (LeftWS). You can call them by placing:
=RightWS()
in a cell.

Function RightWS() As String
    Application.Volatile
    For i = Worksheets.Count To 1 Step -1
        If Worksheets(i).Name = Application.Caller.Parent.Name Then Exit For
        RightWS = Worksheets(i).Name
    Next i
End Function

Function LeftWS() As String
    Application.Volatile
    For i = 1 To Worksheets.Count Step 1
        If Worksheets(i).Name = Application.Caller.Parent.Name Then Exit For
        LeftWS = Worksheets(i).Name
    Next i
End Function

To use the worksheet name in a formula you will need to use the INDIRECT function. For instance:
=INDIRECT(leftws() & "!A1")
will return the value in A1 in the worksheet to the left of the one with the formula.

If the sheet to the left of the current one is called, for instance, 2014-May then the contents inside the parentheses of the INDIRECT statement resolve to:
"2014-May" & "!A1" which further resolves to "2014-May!A1. INDIRECT also runs that string as if it had an equals sign in front of it so you get back the value in A1 from the sheet called 2014-May.

You can use it in a VLOOKUP like this:
=VLOOKUP(D7,INDIRECT(rightws() & "!D2:E4"),2,FALSE)

I hope this helps,

 

[Purple Dragon]

Well, Rick, don't know about the O.P., but this certainly did help me! Thank you very much.

One thing I discovered in my case was the need for quotes around the tab name. My tabs are named 2017-04, 2017-03, etc. Since they contain what could be misconstrued as a formula, this is what my INDIRECT statement looks like: =INDIRECT("'" & RightWS() & "'" & "!A1"). I'm sure you know this, but in case someone reading this thread and is having issues, this may help.

Thanks again for your post!

 

[RickXL]

Hi,

Thanks for letting me know and thanks, too, for the enhancement.


Regards,