Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Dec 2002
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cover Sheet results

    I have a cover sheet (listing the Months from which I get results like name, apartment number, payments)
    Months
    A2 Jan-April 2015 typed in
    A3 May-Aug 2015 typed in
    A4 Sept-Dec 2015 typed in

    My worksheets tabs are also called Jan-April 2015 and so on
    my formula right now in B2 is
    =IF(ISNA(VLOOKUP($B$4,'Jan-April 2015'!$A$2:$J$3374,3,FALSE)),"",VLOOKUP($B$4,'Jan-April 2015'!$A$2:$J$3374,3,FALSE)
    I would like not having to type in A2 Jan-April 2015 but instead refer to the worksheet tab Jan-April 2015. Something like in A2 =(worksheetName)-which would be Jan-April 2015. Then how would the formula be wrote in B2
    Sorry I'm not very good at explaining things.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi buckshot

    You didn't say what version of Excel you are using, so let's start by assuming it's Excel2003.

    You can use this custom function to return the sheetname of a chosen cell:
    Code:
    Function tabName(zCell As Range, Optional zRef As Boolean) As String
    
    Application.Volatile
    tabName = zCell.Parent.Name
    If zRef = True Then tabName = "'" & tabName & "'!"
    
    End Function
    see attached file with example.

    You use the function as =tabName(cell) where cell is any cell on the required sheet (I would just point it to cell A1 on the required sheet)
    If the sheet name is subsequently changed, the formula cell will update to show the new sheetname.

    Now, if a sheetname has spaces in it, or to use it with an INDIRECT formula, you need to 'wrap' the sheetname with apostrophes and add an exclamation mark at the end e.g. like 'Jan-April 2015'!
    The function will do this for you if you use the optional value of True e.g. as in
    =tabName(mycell, True) where mycell is your chosen cell on the chosen sheet.

    Now, as for the formula you want, this could be simplified if you are using Excel2007 or later.
    So I'll wait until you tell us the version you are using first.

    zeddy
    Attached Files Attached Files

  3. #3
    2 Star Lounger
    Join Date
    Dec 2002
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Zeddy, I do have excel 2007

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Zeddy,

    Nice snippet! You could have also used the line:

    tabName = Application.Caller.Parent.Name

    Then no range parameter would be needed. In the cell the formula would be tabName()

    Maud

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Maud

    ..but, as in the posted sample file, if you wanted to return the sheetname of sheets other than the current sheet???
    I'm assuming that the person wants to say, change the 2015 to 2016 in the tab sheets, but not have to manually update the [Cover] sheet.
    Am I missing something?

    zeddy

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Buckshot

    ..I haven't forgotten - I'll post a reply soon.

    zeddy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •