hiding selected rows

J

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
 
H

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)
Click to expand...

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.
 
Top