On Friday, March 2, 2012 3:35:24 AM UTC-6, bg12 wrote:
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 !!!!




--
bg12

I suggest using a macro. Send your file to dguillett1 @gmail.com with a complete explanation and examples.