Conditonal Formatting a Cell Based on Other Cells

[fasterthanyours]

Here's what I got.

I'm doing a Comparison of Price quotes from some different vendors and I want to highlight which one is the lowest.

4 Rows with Totals
The Cells are B21, B43, B65, B87

I need each value in each location to be compared to the other 3.

The other thing is that I also C through V that need the same thing.

Thanks.

[modytrane]

See the attached example.
Look at the formula in conditional formatting.
Hope this helps.
modytrane

[fasterthanyours]

Very Cool.

I set the Max as well...

Is there a way to format the values in between as Orange?

[modytrane]

see the attachment.
you only have to set up for two conditions. third will be the default color.
modytrane.

[fasterthanyours]

Very Cool again. I kind of figured that out messing around with it, TY.

Ok so I made a Separate Sheet to do the following:
I used the MIN to find the minimum in each Column on the Quotes Sheet.
Now what I'd like to do is fill the Cell B21 on my Lowest Sheet with the Vendor from B23, B45, B67, B89 on the Quotes Sheet that has that lowest price in B20.

Any thoughts please?

[modytrane]

It's difficult to work with all of your cell references.
It would make everyone's life easy [less painful], if you attach a sample.
Just like I created a sample to offer the solution.
Attach a workbook with sample data and explain your desired results.
modytrane.

[fasterthanyours]

OK...Here it is...

It's not top secret.

[modytrane]

see the attached file.
Not very cute, but it works.
ROWS 21 and 22 on Lowest Prices sheet are hidden.
You can unhide them to see the formulas.
It became a bit more complicated, because your lowest price was showing up on rows other than 21,43,65,87 and 109.
For example the first one $8,999.00 shows up on row 41 and 43.
In some cases, it showed up a couple of times in the column.

Anyway, hope it helps.
modytrane

[fasterthanyours]

Very cool. I was able to tweak your formulas to help populate the columns above with the different information since some of mine was not static.

And THEN! I hit Alt-F4 to close something else and clicked "No"! 1 Hour lost this morning!!!!

TY though, worked great.

[fasterthanyours]

Modytrane,

So after playing around with it all day I figure out a few really cool things, but I've hit a roadblock on the Lowest Prices sheet.

Look at C21 on the Lowest Prices Sheet. It's returning Row Assignment 43. So it's saying that the Value in C20, exists in C43 on the Quotes sheet. And there's no data in C43 on the Quotes Sheet. It's also happening with the "Server Options" Far Right.

Oh I just noticed something, when the Value in C21 on the Quotes sheet is the lowest (The Dell Prices), C21 Assigns Row 43 on the Lowest Prices sheet, every time except Column A & T.

Basically it's cause the wrong vendor to appear.

I've Attached it again.

Thanks

[modytrane]

OK, This should do it.
See the attached file.
modytrane

[fasterthanyours]

HA I owe you a beer a too.

[fasterthanyours]

Man it still isn't working right.

I put in lower values in the MicroAge section than the Dell and it comes up with Insight as the low value and there's nothing in those columns

[fasterthanyours]

Ok Never mind. I went a different direction and decide to do one list and separate rows for each Vendor. That seemed to function better.

[modytrane]

I see what the problem was.
I have fixed it, in case you want to use it.
I still see one more issue. It relates to quantity. I see that on your sheet "Lowest Prices", Row 19 [Quantity] is manually entered. The Lowest Price below reflects total price [sometimes it's for more than one item], copied from Quotes Sheet. So this sheet doesn't look right.
modytrane.