Solved Creating a list omitting blank cells

September 18, 2016 at 19:38:18
Specs: Windows 10
Hi

I've got a list which is already have formula to read from other cells. I would like to combine this 2nd list which may return blank cells.

example:
A1 = PAPER
A2 = TAPE
A3 = (BLANK)
A4 = PAPER
A5 = (BLANK)
A6 = MISCELLANEOUS

They all have relevant cost which I would like to add up once a list has been created to generate an estimate without any blank lines.

What's the best formula to look up for say, A2 and if blank, look up A3 or A4...?

Thank you.


See More: Creating a list omitting blank cells

Reply ↓  Report •


#1
September 19, 2016 at 05:56:16
✔ Best Answer
Without more specific re: the layout of your workbook, it's kind of hard to offer a specific solution. It is not clear to me if the list you posted is hard-coded into the cells or if the values are the result of a formula. The solution may depend on how the values ended up in the cells.

A simple solution might be to sort your list which would eliminate the blanks.

A more elegant solution might be to use this formula in Column B, or any Column you chose as long as you reference it correctly in the formula. You'll note that the last argument references the Column in which the formula resides.

=IFERROR(INDEX($A$1:$A$6,AGGREGATE(15,6,(ROW($A$1:$A$6)-ROW($A$1)+1)/($A$1:$A$6<>""),ROWS(B$1:B1))),"")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •
Related Solutions


Ask Question