How to make dynamic charts at Excel by using non-blank values?

[comeacross]

Dear all.

Now I met a problem. I want to make dynamic pie charts at EXCEL by using non-blank values. I have followed one of the popular videos which teaches how to do this step by step. However, when I use this function it does not work.

The formula that I used is :
IF(COUNT($K$61:$K$127)>=ROWS(K$61:K61), INDEX($B$61:$B$127, MATCH(SMALL(IF(LEN($K$61:$K$127)>0,ROW($K$61:$K$127),""), ROWS(K$61:K61)), ROW($K$61:$K$127),0),1,"")

I do not know where goes wrong. Any ideas about how to solve this problem?

Thanks a lot.

Best wishes,
Serena

 

[comeacross]

Hi, dear all,

I just made another adjustment for the formula, the new formula is :

=IF(COUNT($K$61:$K$127)>=ROWS(K$61:K61);INDEX($B$61:$B$127;MATCH(IF(LEN($K$61:$K$127)>0;ROW($K$61:$K$127);0);"")))

This time the formula is in a correct pattern, however the result turns to "#VALUE!". There is an error value. Any ideas about how to solve this one? I think

 

[comeacross]

Hi all,

Sorry for this post which I did not describe my questions clearly. So I made another post (https://www.mrexcel.com/forum/excel...amic-charts-excel-using-non-blank-values.html) with the same title which describes the questions more clearly. Thank you so much for your help.