Help Please!

[DonVman]

Hello,

I have this formula to read a particular area in my spreadsheet and query the results to another part of my spreadsheet so I can create bar charts from that new area of records. The problem I am having is when the code runs into cells that are "blank" or "empty" it still places in 0 or 1/0/1900. These cells that exist in the main spreadsheet do not have any data in those cells so I need the code make the new cells be "Empty" or "blank" etc.. I tried placing an IFERROR into this code but I can't get it to work. Any ideas?


=IF(ROWS(BH$2:BH2)<='Test Chart'!$BD$2,INDEX(INDIRECT(BH$1),SMALL(IF(SingleMulti='Test Chart'!$BC$2,ROW(SingleMulti)-ROW($AU$2)+1),ROWS(BH$2:BH2))),"")

Sorry if I messed up and not properly formatted the code correctly.

 

[Khalid NGO]

Hi,

Not sure, this is just a stab in the dark!

=IFERROR(1/(1/IF(ROWS(BH$2:BH2)<='Test Chart'!$BD$2,INDEX(INDIRECT(BH$1),SMALL(IF(SingleMulti='Test Chart'!$BC$2,ROW(SingleMulti)-ROW($AU$2)+1),ROWS(BH$2:BH2))),"")),"")

Regards,

 

[DonVman]

Hi,

Not sure, this is just a stab in the dark!

=IFERROR(1/(1/IF(ROWS(BH$2:BH2)<='Test Chart'!$BD$2,INDEX(INDIRECT(BH$1),SMALL(IF(SingleMulti='Test Chart'!$BC$2,ROW(SingleMulti)-ROW($AU$2)+1),ROWS(BH$2:BH2))),"")),"")

Regards,

Close. I forgot to mention that I do have cells with "Text" as well and your code wipes out those cells. It appears to work on the cells that are "Blank" though and where there is a Number it does not wipe them out. Really close though.

 

[Khalid NGO]

Hi,

Yes it was just a wild guess as I haven't seen your data.

If you can post a sample file, chances are high you may get more targeted answers.

Regards,

 

[DonVman]

Ok, Thnx for the support. I have included a sample file. I hope that I explained clearly enough. I am sure once you see how I have my data organized it will hopefully make sense. I am of course open to criticism. I know I could possibly clean up the data better. Any recommendations are welcome...

Thanks

 

[NARAYANK991]

Hi ,

The problem is not with the formula.

The problem is with the fact that you have 4 columns of data , in columns L , N , P and R.

These 4 columns have differing amounts of data , with the columns having 6 , 9 , 2 and 3 values respectively.

However , in order to fill the output columns BS , BU , BW and BY with this data , the formulae in these columns are using one single value in $BD$2 to decide whether these output columns should have blanks or valid data.

You need to have 4 different counts in 4 different cells , and use each of these cells in the formulae in columns BS , BU , BW and BY. Only then will these columns have blanks where there are no values.

Narayan

 

[DonVman]

Narayan,

Thanks for the input. I was able to make the changes to the spreadsheet and it works fine as you suggested. Question, currently I am querying on "S" or "M"... How can I get this to also include "ALL" so basically, in the end, I would have 3 options "S", "M", "ALL" and the records will show accordingly. Also, do you see anything where I can improve the way I am pulling information? I am sure I can improve what I am doing. I'm just asking cause I would like to learn if there is an easier solution to accomplish the same task etc.. I welcome any ideas that you or any other have...

Thanks again for your help.

I have attached the updated spreadsheet.

 

[Haz]

You could use =TEXT(IF(...),"") to hide the 0s.

At columns CR, CU, etc., use ="" instead of =0 to get rid of the errors.