Keeping reference in formulas when data is deleted and then re added

[nguerra]

Hi Everyone,
I have this formula in a cell now.

"=IFERROR(HLOOKUP(Q5,'Sheet1 Data'!$F$9:$V$13,2,FALSE),"0")"

However when there is an update to the data, the original data is deleted and new data is replaced resulting in

=IFERROR(HLOOKUP(Q5,'Sheet1 Data'!#REF!,2,FALSE),"0")

Is there anyway to keep the reference during an update?
Any help would be greatly appreciated
Nick

 

[Waimea]

Hi,

replace "=" with "=#".

Then change the data, and replace "=#" with "=".

 

[Eric W]

Try INDIRECT:

=IFERROR(HLOOKUP(Q5,INDIRECT("'Sheet1 Data'!$F$9:$V$13"),2,FALSE),"0")

 

[nguerra]

Perfecto! Thank you Eric!

 

[Eric W]

Glad we could help! Thanks for the feedback.