Formula to extract a list from an existing list ignoring 0 values

Good Morning All,

I found a formula on line to eliminate blank cells in an original list and create a list without the blank cells, this formula was =IFERROR(INDEX(AreaBlanksRange,SMALL((IF(LEN(AreaBlanksRange),ROW(INDIRECT("1:"&ROWS(AreaBlanksRange))))),ROW(A1)),1),"")

I created a data ranges for this formula to read from, The problem I have is that the original list I have contains formulas in every cell, so excel does not pick up the cells as blank cells, I can set my formulas in my original list so that instead of appearing blank it shows a 0 in the cell instead, can the above formula be adapted to ignore 0 values instead of blank cells?


Example below of my original list and how I want the new list to appear:

20 20
0 30
30 40
0
40
0

I would also need this list to work for a formula picking up text, but would still show a 0 if I want to ignore this cell :-)

Thanks

Mark

Hi All

Ho do you upload a spread sheet apologies for sounding daft but cant see the usual button you would in an email

Thanks

Mark

Hi Again

Scratch that I seem to have managed it but didn't think it had worked,

in the sample spread sheet, the data on the first tab is all transferred into the sheet 1 tab in columns A and B, I am trying to get columns D and E to skip the zeros, the formula in this is currently set to ignore blanks.

Thanks

Mark

Hi

Thanks for this reply, I am hoping not have to replace the formulas each time in case the users accidentally overwrite their master file, I have a back up at the moment of copy and paste special the values from the data, filter the zeros in this range and then highlight the cells and hit delete, this then clears the cells :-) I just know if it can ignore blank cells it must be able to ignore a certain value but I am not that good in excel :-)

Cheers

Mark

Hi Pepe

Thanks for the reply, the problem I have is that there is another sheet to print out labels per pallet and I don't want it to print out all the potential zeros, for now looks like I will have to keep to the current plan of copy and paste special data, filter out the zeros highlight the cells and hit delete, this then makes the cells blank to excel and the original formula works :-)

I just wish I knew how to read the array formula of =IFERROR(INDEX(AreaBlanksRange,SMALL((IF(LEN(AreaBlanksRange),ROW(INDIRECT("1:"&ROWS(AreaBlanksRange))))),ROW(A1)),1),"")
so I knew which bit to manipulate to make it ignore zeros instead of blanks :-)

Cheers

Mark

Cheers

Mark