Automatically update formula using reference to sheet not present

[johnbrownbaby]

Hello,

I would like to firstly check if a specific sheet exists, then if TRUE, extract a value from that sheet.

I do this in two stages:
Step1:- Cell B3 - If sheet "JAN2018" exist, then return 0, else FALSE -

=IFERROR(INDIRECT("JAN2018"&"!A1"),"FALSE")

Step 2:- Cell B5 - If cell B3 = 0, then get value from Sheet

=IF(B3=0,0,'JAN2018'!B3)

I use this 'JAN2018' in a lot of other formulas.

How to go about making the sheet name 'JAN2018' in the formulas above dynamic, so that if I update the sheet name to say 'JAN2019', I don't have to go and change the formulas in a bunch of the other places where the "JAN2018!..." was used?

Thanks!

 

[Peter_SSs]

I do this in two stages:
Step1:- Cell B3 - If sheet "JAN2018" exist, then return 0, else FALSE -

Step 2:- Cell B5 - If cell B3 = 0, then get value from Sheet

This doesn't seem consistent to me, False in one place and 0 in the other.

How to go about making the sheet name 'JAN2018' in the formulas above dynamic, ...

See if this works for you. Take another empty cell (I've used cell C1) and format it as Text. In that cell enter JAN2018
Then ..
B3: =IFERROR(INDIRECT("'"&C1&"'!A1"),0)

B5: =IF(B3=0,0,INDIRECT("'"&C1&"'!B3"))

Then you can just change the text in cell C1