automatic summary page
 

I have a two page order sheet . You can fill in quantities ordered for each
item. I want a summary order that only populates the lines ordered into a
summary sheet. Is this possible to program in excel? If so, how?
--
Anita

automatic summary page
 

Use this as a model, placed in your Summary sheet:

It checks if the value in Sheet1 (Order sheet) , column A is 0 (e.g.
Quantities) and the answer is true, copies the data in the INDEX range on
Sheet1 - in this case quantities - to your summary sheet.

If you are copying contiguous columns from the Order sheet, you can copy the
formula across for the required number of columns and the INDEX range will
change from A to B etc and then down for as many rows as required (until
blank line).

=IF(ISERROR(SMALL(IF(Sheet1!$A$1:$A$200,ROW(Sheet 1!$A$1:$A$20),""),ROW($A1))),"",INDEX(Sheet1!A$1:A $20,N(SMALL(IF(Sheet1!$A$1:$A$200,ROW(Sheet1!$A$1 :$A$20),""),ROW($A1)))))

Change ranges to suit

HTH

"Anita" wrote:

I have a two page order sheet . You can fill in quantities ordered for each
item. I want a summary order that only populates the lines ordered into a
summary sheet. Is this possible to program in excel? If so, how?
--
Anita

automatic summary page
 

Thank you. Unless I am using this incorrectly, it will return a value in one
cell in the same reference area on the summary sheet. I would basically like
to fill in the same order sheet into a summary that only populates the lines
that have a been ordered. So if I had 40 lines of widgets on a spreadsheet
two pages long, and a customer enters QTy 1 on line two to order widget 2,
and enters QTY 3 on line 39 to order Widget 39, then only those two rows -
the complete row- will poulate on the summary page.
--
Anita


"Toppers" wrote:

Use this as a model, placed in your Summary sheet:

It checks if the value in Sheet1 (Order sheet) , column A is 0 (e.g.
Quantities) and the answer is true, copies the data in the INDEX range on
Sheet1 - in this case quantities - to your summary sheet.

If you are copying contiguous columns from the Order sheet, you can copy the
formula across for the required number of columns and the INDEX range will
change from A to B etc and then down for as many rows as required (until
blank line).

=IF(ISERROR(SMALL(IF(Sheet1!$A$1:$A$200,ROW(Sheet 1!$A$1:$A$20),""),ROW($A1))),"",INDEX(Sheet1!A$1:A $20,N(SMALL(IF(Sheet1!$A$1:$A$200,ROW(Sheet1!$A$1 :$A$20),""),ROW($A1)))))

Change ranges to suit

HTH

"Anita" wrote:

I have a two page order sheet . You can fill in quantities ordered for each
item. I want a summary order that only populates the lines ordered into a
summary sheet. Is this possible to program in excel? If so, how?
--
Anita

automatic summary page
 

If you check your quantity field ( 0?) then it will return data only for
those rows which met this condition. The formula only returns the value of
cell in the INDEX range (into the cell in which the formula exits). If you
need to return several columns of data, the INDEX range needs to reflect the
ranges (columns) in your ORDER w/sheet.

If you want send a sample w/book to:

toppers <at NOSPAMjohntopley.fsnet.co.uk

remove NOSPAM

HTH

"Anita" wrote:

Thank you. Unless I am using this incorrectly, it will return a value in one
cell in the same reference area on the summary sheet. I would basically like
to fill in the same order sheet into a summary that only populates the lines
that have a been ordered. So if I had 40 lines of widgets on a spreadsheet
two pages long, and a customer enters QTy 1 on line two to order widget 2,
and enters QTY 3 on line 39 to order Widget 39, then only those two rows -
the complete row- will poulate on the summary page.
--
Anita


"Toppers" wrote:

Use this as a model, placed in your Summary sheet:

It checks if the value in Sheet1 (Order sheet) , column A is 0 (e.g.
Quantities) and the answer is true, copies the data in the INDEX range on
Sheet1 - in this case quantities - to your summary sheet.

If you are copying contiguous columns from the Order sheet, you can copy the
formula across for the required number of columns and the INDEX range will
change from A to B etc and then down for as many rows as required (until
blank line).

=IF(ISERROR(SMALL(IF(Sheet1!$A$1:$A$200,ROW(Sheet 1!$A$1:$A$20),""),ROW($A1))),"",INDEX(Sheet1!A$1:A $20,N(SMALL(IF(Sheet1!$A$1:$A$200,ROW(Sheet1!$A$1 :$A$20),""),ROW($A1)))))

Change ranges to suit

HTH

"Anita" wrote:

I have a two page order sheet . You can fill in quantities ordered for each
item. I want a summary order that only populates the lines ordered into a
summary sheet. Is this possible to program in excel? If so, how?
--
Anita

automatic summary page
 

Did you receive my e-mail?
--
Anita


"Toppers" wrote:

If you check your quantity field ( 0?) then it will return data only for
those rows which met this condition. The formula only returns the value of
cell in the INDEX range (into the cell in which the formula exits). If you
need to return several columns of data, the INDEX range needs to reflect the
ranges (columns) in your ORDER w/sheet.

If you want send a sample w/book to:

toppers <at NOSPAMjohntopley.fsnet.co.uk

remove NOSPAM

HTH

"Anita" wrote:

Thank you. Unless I am using this incorrectly, it will return a value in one
cell in the same reference area on the summary sheet. I would basically like
to fill in the same order sheet into a summary that only populates the lines
that have a been ordered. So if I had 40 lines of widgets on a spreadsheet
two pages long, and a customer enters QTy 1 on line two to order widget 2,
and enters QTY 3 on line 39 to order Widget 39, then only those two rows -
the complete row- will poulate on the summary page.
--
Anita


"Toppers" wrote:

Use this as a model, placed in your Summary sheet:

It checks if the value in Sheet1 (Order sheet) , column A is 0 (e.g.
Quantities) and the answer is true, copies the data in the INDEX range on
Sheet1 - in this case quantities - to your summary sheet.

If you are copying contiguous columns from the Order sheet, you can copy the
formula across for the required number of columns and the INDEX range will
change from A to B etc and then down for as many rows as required (until
blank line).

=IF(ISERROR(SMALL(IF(Sheet1!$A$1:$A$200,ROW(Sheet 1!$A$1:$A$20),""),ROW($A1))),"",INDEX(Sheet1!A$1:A $20,N(SMALL(IF(Sheet1!$A$1:$A$200,ROW(Sheet1!$A$1 :$A$20),""),ROW($A1)))))

Change ranges to suit

HTH

"Anita" wrote:

I have a two page order sheet . You can fill in quantities ordered for each
item. I want a summary order that only populates the lines ordered into a
summary sheet. Is this possible to program in excel? If so, how?
--
Anita