Greetings:

I use an Excel spreadsheet to do estimating for a building contractor. The
spreadsheet has two pages, a Worksheet page and a Proposal page. As we
receive bids from different trades (electrical, plumbing, HVAC, etc) we enter
these bids into the Worksheet page in the format Quantity times Amount
(formula is =SUM(quantity cell*amount cell). For example, an electrician
bids $1,000 to do wiring for a particular job, so we would enter (1) in the
Quantity cell and (1000) in the Amount cell. The result is the bid for that
vendor for that trade, $1000 in this case. This amount would then be
displayed in a "Total" cell for that trade, and also on the Proposal page
under "Electrical," assuming of course there are no other bids.

We generally get several bids from each trade, and then try to go with the
lowest bid for a given trade to use in our Proposal. Currently, if multiple
bids are entered into the Worksheet page, the spreadsheet ADDS each bid
together (formula is =SUM(beginning of range:end of range) then displays the
SUM of all bids in the Total cell for that trade (and again on the Proposal
page). The only way to keep this from happening is to put a zero in the
Quantity cell for a particular bid in order to "fool" the spreadsheet into
not adding that to the "Total" cell. For example, let's say I have two
Electrical bids, one from Company A and one from Company B. Company A has
the low bid of $1000 and Company B has a higher bid of $1200. I want Company
A's bid to show up on the Proposal page so I enter (1) under Quantity and
1000 under Amount for Company A, and (0) under Quantity and 1200 under Amount
for Company B. This prevents the "Total" cell from adding the two bids
together and displaying the result on the Proposal page. This method works
OK but when we receive large numbers of bids it can cause confusion. Plus, I
still want the Worksheet page to reflect the higher bid, I just don't want
that amount included in the Proposal.

What I want is for my "Total" cell for each trade to compare each individual
"Amount" cell above it, determine which "Amount" (or bid) is LOWEST, and then
display ONLY THAT AMOUNT on the Proposal page for that particular trade. Is
this possible?

Thanks in advance!

Ray