Wrong with Offset formula

[sachar]

Dear All,

With reference to the attach sample file, May I know what is the wrong with OFFSET formula in the worksheet column “Q” highlighted with Yellow colour that is returning the “#VALUE”.

Please help me?

 

[NARAYANK991]

Hi ,

There is nothing wrong with the formula except the way it has been used.

If you want the SUM of all the values in the column C , then the correct formula to use would be :

=SUM(OFFSET($B$9,0,1,COUNTA($B9:$B66),1))

Narayan

 

[sachar]

Dear Narayank,

I want to use this for name range, and after that apply in Data Validation for making the Dashboard to select the period.

Thanks for any kind of help.

 

[NARAYANK991]

Hi ,

See your file now.

Narayan

 

[bosco_yip]

Dear Narayank,

I want to use this for name range, and after that apply in Data Validation for making the Dashboard to select the period.

Thanks for any kind of help.

Another option,

D4 :
=OFFSET(B8,MATCH(C2,B9:B66,0),MATCH(C4,C8:T8,0))

Regards
Bosco