Cover Sheet results

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

Thanks Zeddy, I do have excel 2007

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Hi Buckshot

..I haven’t forgotten – I’ll post a reply soon.

zeddy

Reply | Quote