I have a storage facility where I am trying to use Excel to track all the units. I understand conditional formatting well enough to change the text and cell colors for the unit size and current status – for example, the status cells for empty units are colored yellow — in my data sheet (which is Sheet2):

  Information worksheet (called Sheet2)
            (This cropped image links to its original, unnecessarily huge version.)

Note that Unit 26 (in Row 27), and several others, have status = Empty, and therefore are colored yellow.

I have another sheet that is formatted to look like the floor plan of the building.  It has merged cells (because the storage units are not all the same size) which contain the unit numbers (corresponding to the Unit # in Column A in Sheet2).

Floor plan

I want to color the units in the floor plan based on the status information.  For example, since unit 26 is empty, I want it to be colored yellow.  How can I do that?

  • This is a pretty poor question by Super User standards. Your first paragraph asks, “can I change two cells the same color that have different text/values from a drop down list?” It’s not until the third paragraph that we get a clue what two cells you’re talking about, and what their relationship is. The bit about “cells … that have different text/values” is just confusing, and I have no clue what this question has to do with a drop down list.” (OK, I guess that you have a list of possible values — “Empty”, “Current”, “1 Month Behind”,  … (Cont’d) – Scott Jul 3 '16 at 6:47
  • (Cont’d) …  “2 Months Behind”, etc. — for the “Unit Status” in Column C, but that doesn’t really have anything to do with the question.)  When people hear the word “graph”, they generally don’t think of things like floor plans.  You should have explained that you have a worksheet formatted to look like the layout of the building, and it would have been good to show a mock-up of what result you want to get.  Finally, you should say what version of Excel you are using.  I’ve made an attempt to clean up your question, but I have no way of knowing what version of Excel you are using. – Scott Jul 3 '16 at 6:47

It’s much simpler than the other answer suggests.  Simply select the entire floor plan worksheet (i.e., Sheet1) and create a conditional formatting rule.  Use a formula to determine which cells to format:

=VLOOKUP(A1, Sheet2!$A$1:$D$203, 3) = "Empty"

For each cell, which represents a storage unit, this takes the number in the cell, which is the number of the unit, looks it up in the information worksheet (Sheet2, range $A$1:$D$203), retrieves the Unit Status (from column 3), and tests it the way you want.  It should look like this:

          New Formatting Rule (as described above)
  Conditional Formatting Rules Manager
where $A$1:$AR$24 is the range of the floor plan.

For example, from these data:


I got this coloring:

detail of floor plan with coloring
(This detail image links to the full-size version.)

I did the above in Microsoft Excel 2013.  I vaguely recall that older versions of Excel didn’t allow conditional formatting formulas to access data on another worksheet.  If you have that problem, just dynamically link your status information from Sheet2 onto the “Unit Graph” (floor plan) worksheet (e.g., in rows 101-303), and go from there.

  • Thanks for that info Scott. I did what you said and it worked liked I needed it to. Sorry for the confusion when I asked the question. I can use excel with basic knowledge so I wasn't sure how to ask the question. I also just update my computer from a Mac to a PC with Windows 16 so I am trying to figure out how to use everything again. – Mike Jul 5 '16 at 14:08

I thought it was tougher than this, but it turns out to be quite simple.

  1. Create a new field, a new column in Sheet2. Name it Map, which relates to your Graph Sheet. Your first entry will be a formula for unit 1.
  2. Formula for unit 1: if(c2="Empty", True, "")
  3. One by one. Start with Unit 1. Select Unit one's cell, or cells, then add a conditional format according to your Sheet2.
  4. Formula for Conditional format, unit 1, Graph Sheet, Rule: Formula:=Sheet2!$E$2 Applies to =$cells_that_occupy_unit1 (see image)
  5. When the field "Map" is true, your conditional formatting on GraphSheet will render.

enter image description here

My cells and sheets may have different names than yours.

Make it elegent: Use naming convention (e.g. E2=unit1) enter image description here

  • So, you're suggesting that the OP manually assign names to 202 cells on Sheet2 and 202 ranges on Sheet1, and also manually apply conditional formatting to 202 ranges on Sheet1 — and you call this simple and elegant?? – Scott Jul 3 '16 at 6:59
  • @Scott Yes I do. You need an attitude adjustment pal. – ejbytes Jul 3 '16 at 7:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.