How do I get excel to perfom a real time intelligent sort from multiple columns

[mouseman]

Hi, I'm looking to make a spreadsheet that will intelligently sort through the cells in specific rows and put the lowest valued cell on a different sheet of the workbook, while also depositing the contents of the cell to the immediate right of it to the right of cell in the new sheet as well. The one last thing I'm hoping it can do is put the header of the column in either the cell to either side of the newly created cells in the new sheet, or convert it into a note. My hope is that this formula will automatically go through my pricing worksheet and sort what vendor had the lowest price, with their lead time next to it, all on a seperate, easier to reference sheet in the same workbook, because the spreadsheet as it is now is a huge spralling affair. Any thoughts or ideas?

Thanks!
Mouse

[Dennis7849]

If you can setup and attach a sample workbook that shows what you are expecting, it would be easier to understand your needs.

[mouseman]

Sure here you go. I have put a few examples of the kind of results I would like back. Thanks for looking at this for me, I have really no experience in this complex of a formula.On-going hardware pricing need and purchasing spreadsheet ver 2.0.xlsx

[mouseman]

***Edit*** formula for vendor name selection required important change, that I have Bolded, Italized and underlined. Without this change, you will not be able to drag the formula down and have it function properly.

Ok, this is how I solved it: after messing with VBA for a couple of hours with no success, I first used the min function to determine the lowest price, then I ended up making a series of nested if statements that called for the min to determine which name should end up in the column next to the price, with a master if function to extract the correct information from the series of if/min statements and display it in the appropriate column. I did pretty much the same thing with the lead time column.
Here's a example of the main part of the name selection formula:
=IF(MIN(I3,K3,M3,O3,Q3,S3,U3,W3,Y3,AA3,AC3,AE3,AG3,AI3,AK3)=I3,$I$2,IF...etc
Here is what I used to compile the formulas into one cell and extract and display the correct information:
=IF(H8>0,H8,IF(H9>0,H9,IF(H10>0,H10,0)))
The formula to get the lead time to display properly is extremely similar to the name selection formula, with one very important change, noted in bold:
=IF(MIN(I3,K3,M3,O3,Q3,S3,U3,W3,Y3,AA3,AC3,AE3,AG3,AI3,AK3)=I3,J3,IF...etc

I hope this is helpful!