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$20>0,ROW(Sheet1!$A$1:$A$20),""),ROW($A1))),"",INDEX(Sheet1!A$1:A$20,N(SMALL(IF(Sheet1!$A$1:$A$20>0,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
Similar Threads
