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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

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
Qlemo"Batchelor", 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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
Qlemo"Batchelor", 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
Qlemo"Batchelor", 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:
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

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
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
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.