Excel formula help

[Jagdev Singh]

Hi Experts,
I do not know what is wrong with this formula =IFERROR(Raw_Data!$CH$2,"")
The Raw sheet is getting refreshed every time a macro runs and I want the value of the column CH2 in a sheet/tab next to the macro sheet. Every time a macro get refresh the range CH2 gets changed to CH1201. What I am doing wrong here to lock the value CH2 with the above formula.
Regards,
JD

 

[Asheesh]

Hi Jagdev, Can you share the file please

 

[KenU]

@Jagdev Singh,

I suspect that the macro is inserting rows above row 2. If so, Excel is simply trying to maintain the integrity of the original formula by updating the reference. The best way to correct that is to have the macro update the formula after the rows have been inserted.

Hope that helps.

Regards,
Ken

 

[Jagdev Singh]

Hi Kenu

I think you are right, Macro is the only option to deal with it.

Regards,
JD

 

[Khalid NGO]

Hi Kenu

I think you are right, Macro is the only option to deal with it.

Regards,
JD

Hi JD,

Just a guess
Did you tried:
=IFERROR(INDIRECT("Raw_Data!$CH$2"),"")

Regards,

 

[Jagdev Singh]

Hi khalid

This is fantastic!! It worked it solved the issue.

Regards
JD

 

[Khalid NGO]

Hi khalid

This is fantastic!! It worked it solved the issue.

Regards
JD

Glad it worked.
Thanks for the feedback.