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:</SPAN>
Original List</SPAN>
20
0
30
0
40
0</SPAN>
Required List</SPAN>
20
30
40
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 </SPAN>
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:</SPAN>
Original List</SPAN>
20
0
30
0
40
0</SPAN>
Required List</SPAN>
20
30
40
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 </SPAN>