chart error help

[blazy]

Does anyone know how to avoid getting the error in the picture, when clicking the check box to show or hide the data.

Everything is working fine, I was just wonderinf if I could avoid getting this nasty error everytime I click the checkbox. I know why I get the error but dont know how to avoid it

I have scrollable chart. Could provide excel file if neccessary.

Thanks

 

[Hui]

Looks like a reference in the chart is to another workbook or worksheet where the range has been deleted or gas. #Ref! Error

 

[blazy]

The problem is in OFFSET function, when the value is FALSE. If I try without offset it works.

Any ideas how to write the offset function so it also works if all values are /na?

 

[Debraj]

can you please upload the sample file..

 

[blazy]

I messed up file a little when i copied this sheet into another workbook because im in a hurry, but im sure you will figure it out... thanks!

 

[blazy]

+ how can i repair the scrollbar, when it reaches the end it stops, so i dont get the error...?

 

[Debraj]

try using MIN function to ignore Scrollbar max ..

check this one..

 

[blazy]

GREAT, thank you very much!

 

[blazy]

Hey,

could you please walk me through this code: =OFFSET(Sheet1!$K$6;1;;MATCH(99^99;Sheet1!$K$7:$K$42))

Why 99^99?

Tnx.

 

[blazy]

Hello,

could anyone please explain what does 99^99 do in the formula?

Thanks

 

[Somendra Misra]

Hi blazy,

99^99 is a big number, when such a big number or big text like "zzzzz" is used in MATCH or any LOOKUP function it generally return the last entry in the array. So, using it in MATCH(99^99;Sheet1!$K$7:$K$42) will return last number in K7:K42 position.

Say you array is of A1:A6 {1;10;20;1000;15;90} now you want the last position so using =MATCH(99^99,A1:A6) will return 6. Please also note that you had omitted the last argument of MATCH function so by default it is Less than.

You can also use 9.999999999E+307 in place of 99^99 which is the biggest number Excel can handle.

If you use =LOOKUP(99^99,A1:A6) you will get 90.

I hope this will clear your doubt.

Regards,

 

[blazy]

Thank you very much ninja @Somendra Misra !

 

[Somendra Misra]

You are Welcome!!! @blazy

Regards,