hiding selected rows

[John Gilchrist]

I have a list of items
QUAN DESC PRICE TOTAL PRICE


All the items are listed on the PRICELIST sheet

I want to display only those items with QUAN greater than zero on the
ESTIMATE sheet.

I'd rather do this with some formula which combines IF and HIDE ROW.

If necessary, I could use a macro, but I'd rather not (to keep it simple)

Thanks,
John

 

[Harlan Grove]

John Gilchrist wrote...
....

All the items are listed on the PRICELIST sheet

I want to display only those items with QUAN greater than zero on the
ESTIMATE sheet.

I'd rather do this with some formula which combines IF and HIDE ROW.

If necessary, I could use a macro, but I'd rather not (to keep it simple)

Since formulas *ONLY* return values to their cells, you have no choice:
to do what you want to do, you *MUST* use macros *OR* filters.

Simplest to add another column to the PRICELIST table that uses each
row's DESC field as a lookup value into the ESTIMATE table, returning
the corresponding QUAN from the ESTIMATE table. Since it appears QUAN
appears before DESC, you'd need to use INDEX/MATCH, something like

=MAX(INDEX(ESTIMATE!A$2:A$1000,MATCH(B2,ESTIMATE!B$2:B$1000,0)),1)

Apply an Autofilter to the PRICELIST table including the added column
of formulas, then filter on the column of formulas choosing 1 from the
drop-down list.

 

[Max]

One way,

Assuming source table is in: PRICELIST, cols A to E,
headers in row1, data from row2 down

QUAN DESC PRICE TOTAL PRICE

In ESTIMATE, with the same headers in row1

QUAN DESC PRICE TOTAL PRICE

Put in A2:
=IF(ISERROR(SMALL($F:$F,ROW(A1))),"",
INDEX(PRICELIST!A:A,MATCH(SMALL($F:$F,ROW(A1)),$F:$F,0)))
Copy A2 to E2

Put in F2: =IF(PRICELIST!A2<=0,"",ROW())

Select A2:F2, fill down to say, F100
to cover the max expected extent of data in PRICELIST

The above should return the required results in cols A to E,
all lines neatly bunched at the top

---