I have couple formulas. I want hard code into vba

[sf49ers19238597]

I have worksheet called PTW1 and I also have other worksheet called 1

In cell D2 have this formula      

=IF('1'!D2<>"",'1'!D2,"BWT")

  =IF('1'!G2<>"",'1'!G2,"BWT")

When I delete worksheet called 1 I getting #REF  in D2 & G2 formulas before

I do not want #REF anymore when I delete 1. Next Year I create new 1 worksheet.

=IF(#REF!D2<>"",#REF!D2,"BWT")

=IF(#REF!G2<>"",#REF!G2,"BWT")

I want hard code vba  have both formulas in code. and basic code 

Thanks You

[#2]

Instead of hard coding in VBA you could instead use the function INDIRECT()
=IF(INDIRECT("'1'!D2")<>"",INDIRECT("'1'!D2"),"BWT")

[#3 · Accepted solution]

function INDIRECT()
=IF(INDIRECT("'1'!D2")<>"",INDIRECT("'1'!D2"),"BWT")

=IF(INDIRECT("'1'!G2")<>"",INDIRECT("'1'!G2"),"BWT")

That I do for G2?

Thanks You

[#4]

yes that is what I'm suggesting and it appears that it is working? Glad it helped.

[#5]

@mtarler 

The function doesn't work it is in red can check wrong. if possible can send copy of file

Thanks You

[#6]

@sf49ers19238597  works for me (see attached).  

[#7]

@mtarler 

I delete worksheet called 1 in PTW1 still saying #REF on your file you  posted

Thanks You

[#8]

Did you create a new sheet called 1? Maybe I misunderstood your intentions. I thought you have these formulas that refer to a sheet called "1" but you delete that sheet and create a new sheet that is named "1" and you need those formulas to still work and now use this new sheet "1"? Am i mistaken?

[#9]

Thanks You for formulas It is working now.