Formula Dependant on "text" in sheet name.

[pulsarman605]

Try to explain this the best I can, and hope someone has an answer.

I have a "master" sheet. It has formula that refers to particular cells in another sheet.

I want to be able to have that formula use different cells depending on if it finds "text" in the sheet name.

IE... I copy the master sheet to QLD JAN. I would like it to use QLD data. Copy to NSW JAN I would like the formula to use NSW data etc.

Hope this makes sense and whether it is possible.

 

[Max1616]

Try this:
=IF(ISNUMBER(SEARCH("QLD",SUBSTITUTE(MID(CELL("filename",A1),SEARCH("]",CELL("filename",A1))+1,100),"[","")))=TRUE,"QLD","NSW")

You would change the two bolded values to reflect what you would like to return. So change QLD to the QLD cell reference and NSW to the NSW cell reference.
Also change A1 to the cell reference you are looking at from the master sheet.

Sincerely,
Max

 

[FDibbins]

Hope this makes sense and whether it is possible.

Not really

How would it know you want the totals from OLD Jan rather than NSW JAN? Or any other sheet, for that matter?