Still need a formula that will return a row of information for the outcome from using the MIN functi

Jeff
Instead of all these complicated formulas, can you not get what you need by using Data>Sort?
Celia

Re: To Celia

Celia: I'm not sure that Data Sort will give me what I'm looking for: a totally automated function that doesn't require my taking action every time I import new data into the spreadsheet.

I'd like to be able to download new Prices, parts, and availability from my ACT! database, for different projects, which would replace the prior data. Then, the already-in-place formulas would perform the calculations automatically upon opening the spreadsheet, and then return the rows of information for the columns matching the criteria of lowest price for the designated brand.

That way, all I have to do is open the spreadsheet and "Hit" print, and the pre-set print range would print out a list with just the names, addresses, hours, phone, price, brand, and availability for each of the items that meet the criteria.

The printed list would be my materials requiremnet planning list or a "shopping list" as it were. The beauty of this idea if I can make it work is that I never have to pour over handwritten notes while punching my calculator to find out what I really want to know: who has the best mix of parts at the lowest prices for the desired brands that I need to complete my project? (Automobile repair, home improvement, maintenance, etc.)

Thanks for your reply, Celia! If there is some way to get Data Sort to do the above, I would love to hear how to make it happen.

Jeff Williams

Re: To Celia

I'd like to be able to download new Prices, parts, and availability from my ACT! database, for different projects, which would replace the prior data. Then, the already-in-place formulas would perform the calculations automatically upon opening the spreadsheet, and then return the rows of information for the columns matching the criteria of lowest price for the designated brand. That way, all I have to do is open the spreadsheet and "Hit" print, and the pre-set print range would print out a list with just the names, addresses, hours, phone, price, brand, and availability for each of the items that meet the criteria. The printed list would be my materials requiremnet planning list or a "shopping list" as it were. The beauty of this idea if I can make it work is that I never have to pour over handwritten notes while punching my calculator to find out what I really want to know: who has the best mix of parts at the lowest prices for the desired brands that I need to complete my project? (Automobile repair, home improvement, maintenance, etc.) Thanks for your reply, Celia! If there is some way to get Data Sort to do the above, I would love to hear how to make it happen. Jeff Williams

Jeff
If I understand correctly, you want to look at the row(s) containing the lowest price, made in a particular place(Col H) and the availability(Col I).

This can be achieved by sorting by Col F (price) ascending, then by Col H, then By Col I.
The row(s) you want will be grouped together.

If you use the macro recorder while doing this and then assign the macro to a button, you can do the sort at any time by clicking the button.

If you want the data sorted by some other criteria (e.g. price/brand/availability – or whatever), record another macro and assign it to another button.

Celia

Alternative method

Jeff
It would probably be much better to use Advanced Filter (and record a macro).
Celia

Re: To Celia

Jeff, I'd like to be able to download new Prices, parts, and availability from my ACT! database, for different projects, which would replace the prior data. Then, the already-in-place formulas would perform the calculations automatically upon opening the spreadsheet, and then return the rows of information for the columns matching the criteria of lowest price for the designated brand.

What you want to do can be done easily in a database package that accept SQL or has a visual query language. Access is surely suited to the task you formulated. I imagine ACT! has these facilities.

I also believe Celia's suggestion is the best possible way out if you want to do it in Excel.

By the way, is it possible to get your test data? I could sure use it (if allowed) in teaching Access.

Cheers.

Aladin

I've got a solution to your Cheapest Vendors question. It's too long to describe here. If you want to have a look at it, I can send you (or to anyone interested) the Excel file.

Aladin

akyurek@bart.nl