Conditional Formatting Help! Highlight matching cell in different sheet

[DerekTheDog]

Hello all,

Here is my challenge: I have a spreadsheet made up of 5 different sheets. Four of these sheets represent a different company (Company A, B, C, & D) and show their prices for a range of products at a range of quantities. I then have 1 sheet called “LOWEST PRICE” that pulls whatever price is lowest from Company A, B, C, or D.

The problem is that on the “LOWEST PRICE” sheet I have no way of determining which company that price is being pulled from. I would like to use conditional formatting to highlight each price so that I know which company it is coming from.

Here is a link to a simplified version of the spreadsheet so you can see what I am talking about:
https://docs.google.com/spreadsheets/d/1EkOpFT7Wu5leuusaQz7vWq92NPuah9nY4iDBZxLnoQA/edit?usp=sharing

I realize I could just check manually, but my finalized spreadsheet is going to be a lot more complex and I am hoping I can get this conditional formatting to work.

Any help is greatly appreciated!

Cheers

 

[Special-K99]

This may not work if your version of Excel is too old.

Select LOWEST!B4:I18

Conditional Formatting
New Rule
Use a formula to determine...

=(B4='COMPANY D'!B4)
format as required

Use same formula 3 more times changing the company name and the formatting colour.

NOTE: If more than one company has the same lowest price you will only get ONE company colour

 

[DerekTheDog]

Hmm, I tried that but unfortunately still does not seem to be working. Part of the problem is that I am working with Google Sheets and not Excel

 

[Special-K99]

I dont know anything about Google Sheets.

On old versions of Excel (I cant remember when it changed) Condtional Formatting could not refer to cells on a different sheet.
The workaround for this was to create a Named Range and use that Name instead of an explicit range, ie B4:I18.
Perhaps create a named range and see if that works?