Finding the lowest values within a range and add cell to a formula

Here's one that's sure to test a few people out there.


I have a parts list that is divided into the different suppliers and the different part types.

What I want is to be able to click a button and a formula will find the lowest price within the range and then add that cell to a formula that will calculate the lowest price for an entire collection of the parts.

Pls find attached an example of what I would like to do

I have made a note of the functionality i'm asking for within the sheets in the workbook.

There are 2 sheets with 2 pieces of functionality - I separated them so as to cause less confusion but I would like them both to work together if possible.

There is formula already in the second sheet however I do not know how to incorporate that into the same sheet as the first section.

That is brilliant ... but can you tell me what the conditional formatting is that you used ?
How do I see that ?

because my ranges will be in different cells to those in the workbook you posted back so how do I match the conditional formatting that you used ?

thanks ... I think I understand it.

The only other questions I have are:

If I have my ranges setup with rows and columns BETWEEN them to separate them will that still work ? Would I still include the ENTIRE range block in the formula and just substitute the one cell value as you have done ?
ie: part a = range B2:B6; part b = range b8:b14 etc etc


Will this work with ranges separated over multiple columns and again do I include the entire range block ?
ie: part a = range B2:B6; part b = range E5:e9
(the reason for this second one would be prices from a different company to be setup in a second separate column range.

I'll give it a try and post back if I have troubles.