Fomula change

[samitnair]

Hi

Before: =IF(ISNUMBER(MATCH(F19,'Combined Sheet'!$E$3:$E$65536,0)),INDEX('Combined Sheet'!D3:D65536,MATCH(F19,'Combined Sheet'!$E$3:$E$65536,0)),"")

After: =IF(ISNUMBER(MATCH(F19,#REF!$E$3:$E$65536,0)),INDEX(#REF!D3:D65536,MATCH(F19,#REF!$E$3:$E$65536,0)),"")

The Formula takes reference from another sheet......If the sheet gets deleted the formula also gets changed. Is there a way i can keep the formula unchanged so that when i add the sheet with same name again it automatically picks up the sheet....

 

[Ruddles]

How is the sheet being deleted? Manually or by VBA?

 

[samitnair]

manually...I have to no other go..

 

[schielrn]

You can try this, but indirect isn't always the greatest formula to use because it is volatile and will always re-calc, so you may experience some longer re-calc times:

=IF(ISNUMBER(MATCH(F19,indirect("'Combined Sheet'!$E$3:$E$65536"),0)),INDEX(indirect("'Combined Sheet'!$D$3:$D$65536"),MATCH(F19,indirect("'Combined Sheet'!$E$3:$E$65536"),0)),"")

Hope that helps.

 

[samitnair]

thanks dude....that really helped and am getting a lot to learn