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 !!!!

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.

bg12 brought next idea :
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 !!!!

How I do this is by having a 'master' product sheet and using VLOOKUP
in the order details columns to fetch the billing info for the product
ID entered in the 'Item' column. Depending on your preference, the
product 'Items' can be listed in a Data Validation dropdown.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion