#Ref! error while using OFFSET FUNCTION

[Ratan Bhushan]

Hi Team
Whenever I am using Offset function, it works, yet when I delete the base cell(Entire row or column), the first value in offset function , I always get ref error in range or any other way using via offset. Is there any solution to this problem?
Thanks
RatanB

 

[Asheesh]

Hi,

Try using INDIRECT function for Reference part of OFFSET function..
something like this

OFFSET(INDIRECT("$A$1").....

See the attached..and delete Column A in the attached

 

[Ratan Bhushan]

Hi,

Try using INDIRECT function for Reference part of OFFSET function..
something like this

OFFSET(INDIRECT("$A$1").....

See the attached..and delete Column A in the attached

But when I am using the formula in the following way

it is again giving error

=OFFSET(INDIRECT("$A$1"),1,1,0,0)

I have to use the formula in creating a dynamic range, where every week base cell or row or column is deleted and we get ref error.

Please check.

Thanks
RatanB

 

[Chihiro]

Remove "0,0" portion and see if it works. Or at least replace it with "1,1"
You are basically telling the function to return value of no cell by "0,0".

 

[Ratan Bhushan]

Remove "0,0" portion and see if it works. Or at least replace it with "1,1"
You are basically telling the function to return value of no cell by "0,0".

Hi Chihiro

It is still not working and giving ref error. Plz help.

 

[Chihiro]

Can you upload sample file? I'd be able to better assist you then.