Matching conditional formatting to a map

[Mrslikethesoup]

Hello,

I am trying to match the conditional formatting on one cell to another sheet that has a map. On the customer list sheet I have thousands of customers and the status of their unit in Column C. Column C already has conditional formatting on it to change cell colors based on status. I want the corresponding unit on the 'Map' sheet to turn colors based on the conditional formatting in Column C on the 'Customer List' sheet.

In reality, the customer list is thousands long and the map is very confusing with no real logical order so I was trying to avoid having to redo the conditional formatting on the 'Map' sheet. Because of the type of end users I will have using the sheet, I really want to avoid a macro if possible.

Here is a mock up of the worksheet

 

[kaushik03]

Hi Mrslikethesoup,

I understand the example given in the Customer List sheet but having hard time in understanding your example in Map sheet. Could you plz answer my below questions:

1. What is the criteria to get the corresponding unit on the 'Map' sheet?
2. What is the relation with map and the units?

Could you please explain the above two points clearly to me to help me solve your problem?

Regards,
Kaushik

 

[Mrslikethesoup]

Hi Mrslikethesoup,

I understand the example given in the Customer List sheet but having hard time in understanding your example in Map sheet. Could you plz answer my below questions:

1. What is the criteria to get the corresponding unit on the 'Map' sheet?
2. What is the relation with map and the units?

Could you please explain the above two points clearly to me to help me solve your problem?

Regards,
Kaushik

Yes, thank you for your response.

1. The criteria is the map sheet depicts where the unit physically reside in person. In other words, where the units are on the sheet show where they are in real life. So in real life Unit 1 is next to Unit 2 and has unit 6 in front of it. I can't change where the units are on the map.

2)Hopefully I explained that above but the map shows storage units and where they are on a piece of property. So the goal of the worksheet is to be able to quickly look at the map and see which ones are available and which ones aren't.

Thank you for your help.

 

[NARAYANK991]

Hi ,

Are the options only 4 viz. Rented , Available , Ready To Sale and Out of Order ? Or are there many more ?

If there are only 4 , what you can do is create an identical map on a new worksheet , which mimics the existing map , and which has the Conditional Formatting rules for each unit.

See your file.

Narayan

 

[Mrslikethesoup]

There are 7 statuses total and thousands of units so do the conditional formatting on them all wouldn't really work in setting up a new map next to it.

 

[NARAYANK991]

Hi ,

You are over-estimating the job ; creating an identical map is the work of 5 seconds , the time it takes to make a copy of a worksheet.

With 7 statuses , you need 7 CF rules ; it may take about 15 minutes to put in the formula and the selected colour.

The number of units does not matter , since all you have to do before creating the CF rules is to select the entire data range.

If you can upload your complete workbook , I can do what is necessary and re-upload the file.

Narayan

 

[Mrslikethesoup]

I think it is more complicated than it is coming across. Here is one section of the map.

 

[Somendra Misra]

Hi Mrslikethesoup....

Please see I had implemented some CF in the first sheet that you have uploaded

I had use this formula :
=INDIRECT("'Customer List'!C"&MATCH(B3,'Customer List'!$A$2:$A$7,0)+1)="Out of Order"

for each type like Rented, Available etc.

But your next file contains merge cells. I doubt whether the formula will work over there or not as it has varied range.

Kindly check and inform.
May be other expert members of forum can come up with some rigid solution.

Regards!

 

[NARAYANK991]

Hi ,

I am thoroughly confused now , trying to reconcile your uploaded workbook with your initial post. I quote :

I am trying to match the conditional formatting on one cell to another sheet that has a map. On the customer list sheet I have thousands of customers and the status of their unit in Column C.

Column C already has conditional formatting on it to change cell colors based on status.

I want the corresponding unit on the 'Map' sheet to turn colors based on the conditional formatting in Column C on the 'Customer List' sheet.

In reality, the customer list is thousands long and the map is very confusing with no real logical order so I was trying to avoid having to redo the conditional formatting on the 'Map' sheet.

In your post , you say the list already has CF in it , whereas in your uploaded file , the map has coloured cells , while the list has none.

Can you say what you want done ?

1. The map to be coloured conditionally based on the statuses

2. The list ( either column A or column B or both ) to be coloured conditionally based on the statuses

Narayan