• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional Formatting related to Data Validations

Carley

Carley

New Member
Good Afternoon,
I am trying to create conditional formatting on a training log and I am running into some difficulties.
I am using excel 2010
I am unable to upload so I will try and explain as much as possible.

On Sheet 2 - I have a list of 5 different training types. - Lets say these for instance are lettered 1 to 5, I have assigned an array called topics to these. underneath each of these number is a list of tasks related to the topic
On Sheet 1 - I have a list of ten agents listing downwards from cell A2 down and then ten spaces in the correspoding columns to fill in activities, in cell A1 I have a data validation which looks up array 'topics' then in each of the ten spaces beside the agent name I have the data validation of =indirect($A$!1) set up.

what I would like to happen is that if the agent selects 'topic' 1 in the cell A1 then the tasks available in each of the 5 separate related list come back in different colours.

So far in the conditional formating I have tried =if function and the 'if cell contains' and the related column in sheet 2 but it isn't working.

Is this possible?
 
Hi Carley ,

I am finding it difficult to visualize your setup ; if you cannot upload your file , can you upload a picture of how your data is laid out , including the row and column headers ?

Narayan
 
Here is how I am interpreting Carley's post:
  1. Sheet2 has a list of training topics in cells A1:E1 (e.g. A1 is topic1, B1 is topic2, C1 is topic 3, etc.)
  2. On Sheet2, for each topic column, there are a list of tasks for the topic. For example, in cells A2:A5 are tasks related to Topic1; in cells B2:B7 are tasks related to Topic2; in cells C2:C22 are tasks related to Topic3; etc.
  3. on Sheet1, a list of Agent Names are listed in column A, starting with cell A2. (Foe example, cells A2:A15)
  4. For each Agent in column A, she is expecting to fill in the tasks for that agent in columns B:K (i.e. the "10 spaces"). For example, the 10 tasks for Agent1 in cell A2 would be setup in B2:K2
  5. On Sheet1, in cell A1, she has a dropdown that a user can use to select a topic.
  6. When a topic is selected in A1, she would like to highlight the tasks for each Agent (in columns B:K) that belong to that topic in A1. For example, for Agent 1 in cell A2, if tasks in B2, D2, and K2 belong to Topic 1 (which is the dropdown selection in A1), then those three cells should be highlighted.

Carley: I will look to you to confirm our understanding of your situation.

Cheers,
Sajan.
 
Dear Carley

The problem is that Excel doesn't let you directly use values on other sheets to conditionally format cells. To get around this you need to use named ranges on sheet 2 and then use those named ranges in the conditional formatting rules.
 
You must log in or register to reply here.
Back
Top