How to summarize copied data

[bg12]

Hi,

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

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

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

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

I have tried the below formula:


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

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

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

Thanks !!!

 

[Don Guillett]

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(A1: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 !!!!

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

 

[GS]

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(A1: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