Excel- reference a tab name in a formula

Usually when referencing a tab name in a formula, we'd use:
='tab1'!A1.

Or, in my sheet, since my tabs are named for dates:
='Mar-15'!A1.

On my main tab, I've entered the same dates used for tab names into cells, for use in a formula.  Rather than hard coding 'Mar-15' or 'Mar-16' or 'Mar-...X' into the formuals, I'd like to use the formulas.  Is there a way to do this?  (see attached)
Excel-issue.xlsx
BBRRGGAsked:
Who is Participating?
 
Rob HensonFinance AnalystCommented:
See attached updated file.

The INDIRECT(ADDRESS(...))  formula are still referring to your true date cells as I suspect these dates will be continuing and would be more logical to use them rather than twisting it round to pull the sheet names but I have done that in row 1 just to demonstrate.

The INDIRECT is also wrapped in an IFERROR so that as the new sheets are created the function will recalculate and show the necessary result.

Thanks
Rob H
Excel-issue-v1.xlsx
0
 
Katie PierceCommented:
I think I get what you're going for, but I don't think there's a way to do it, as the name of a tab is not a value.  You'd need a formula that could compare the value in row 1 of the Main Tab to the names of the other tabs, and that can't be done.  I think you do just have to hard code the formulas.
0
 
Tony PittCommented:
I think you can do this, using the INDIRECT function.  You need to build your cell reference with the CONCATENATE function first, so it'll look something like this:

=INDIRECT(CONCATENATE(A1,"!B2"))

/T
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Almost correct, Tony. It is
  =INDIRECT(TEXT(A1; "'dd-mmm'")&"!A1")
for 'Main Tag'!A2.
You might have to use "'dd-MMM'" instead of above, that depends on the language installed.
0
 
Tony PittCommented:
You're (almost!) right, of course, Olemo, though I suspect that should read "mmm-dd" rather than "tt-mmm" :-)
0
 
gowflowCommented:
This is the formula you need to put in A2 and drag to utmost right.
=IFERROR(INDIRECT("'"&TEXT(A1,"dd-Mmm")&"'!A1"),"")

You need to specifically set the date as same format as your sheets or else Excel will read the date you have as a serial number and will never find the sheet. Then you have to account for sheets not existing already this is why you need the IFERROR to prevent display of #REF.

Check the attached.
gowflow
Excel-issue.xlsx
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
The workbook showed worksheet names as '15-Mar', not 'Mar-15'. I've based my answer on the workbook as provided :p.
0
 
Rob HensonFinance AnalystCommented:
Or you can use the INDIRECT with ADDRESS function:

=ADDRESS(RowNumber,ColumnNumber,AbsValue,TypeValue,SheetName)

Row Number / Column Number - fairly obvious
Abs Value - values 1 to 4 depending on what elements you want to make absolute
Type Value - 0 = R1C1 type, 1 = A1 type
Sheet Name - this is where you would reference the cell containing the sheet name.

Check if your cell containing the date isn't actually a date and it is literally text "Mar-15". If it is manually entered as Mar 15 then excel may automatically recognise it as a date and actually enter 01/03/2015 as the value but format it so it shows as "Mar-15".

Another trick that I sometimes use is to pull the sheet name into a cell using a formula for onward use in such as ADDRESS function so that if the sheet name changes it doesn't then create an error.

=RIGHT(CELL("filename",Original!$A$1),LEN(CELL("filename",Original!$A$1))-FIND("]",CELL("filename",Original!$A$1)))

Result of above will be just "Original" without the quotes.

=CELL("filename",reference) will give the whole file name, path and sheet name in format:

Drive:\Directory\Sub-Directory\[Filename.xls]Sheetname

The file name part is within [  ] so doing a FIND on ] will identify the start of the Sheet name.

LEN() gives the number of characters so total LEN less FIND gives number of characters for sheet name.

If you copy this into various cells, you can then just do Find & Replace for sheet names to set it up for each sheet.

Advantage with ADDRESS function is that it doesn't matter if the sheet name includes spaces whereas just referring to a sheet name with spaces when combining in a concatenate string will result in an error.

Thanks
Rob H
0
 
gowflowCommented:
@Qlemo
Your addressing your statement to OP I guess ? If to me then I posted same workbook that was posted by OP !

gowflow
0
 
Rob HensonFinance AnalystCommented:
As suspected, your date references that you want to use as sheet names are true dates formatted. I will amend your file and upload shortly.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
gowflow, that was addresses to Tony. You are correct but late, your solution is "the same" as mine.
0
 
Rob HensonFinance AnalystCommented:
Another advantage of using ADDRESS function is that the Row & Column numbers can be dynamic whereas putting "!A1" in a text string will always stay the same and will need manual intervention if you happen to need to move away from A1.

If all date sheets have the same format, you could maybe use the MATCH or similar function on the Main Tab, referring to just one sheet to get the row and column numbers of the values you want reported.

Thanks
Rob H
0
 
BBRRGGAuthor Commented:
Great solutions, thank you!  Look for a follow up question with a similar application, which applies the method to a sumif function, rather than just a single cell.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.