Hi,

I am using Excel 2010 and have the following problem with summarizing data.

I am creating an order form in Excel, where I have different products listed on 7 different sheets.

I need to create an order summary on a seperate sheet – so whenever a customer chooses/orders a number of products in some of the 7 product sheets, this value including product name etc should be copied to summary sheet and be updated automatically.

I have managed to create simple formulas that copy all this data to one sheet, but these then also copy all the empty rows where nothing is ordered. I still need to be able to summarize this, so the empty rows are removed on the summary.

I have tried the below formula:


=IF(ISERROR(SMALL(IF(A1:A700<"",ROW(A1:A700)),ROW ())),"",INDEX(A:A,MATCH(SMALL(IF(A1:A700<"",ROW(A 1:A700)),ROW()),IF(A1:A700 <"",ROW(A1:A700)),0)))

And it works if data that you need to summarize is inserted manually and other cells are really empty. Then you get your summary with empty rows removed. But in my case, all the cells contain formulas which copy values from the other sheets, so they are not empty although the result of the formula leaves the cell blank when no value is chosen order sheets.

Is there a different formula I should use that would work with cells that contain a formula, but return no value?

Thanks !!!!