Excel- reference a tab name in a formula

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.

You're (almost!) right, of course, Olemo, though I suspect that should read "mmm-dd" rather than "tt-mmm" :-)

The workbook showed worksheet names as '15-Mar', not 'Mar-15'. I've based my answer on the workbook as provided :p.

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

@Qlemo
Your addressing your statement to OP I guess ? If to me then I posted same workbook that was posted by OP !

gowflow

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.

gowflow, that was addresses to Tony. You are correct but late, your solution is "the same" as mine.

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

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.