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 = MISCELLANEOUSThey 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.

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.

Ask Your Question

Weekly Poll

Do you think Amazon can re-invent the convenience store?

Discuss in The Lounge

Poll History