0

What I am trying to achieve is to see if the value of Cell C4 exists in Column F.

If it exists in Column F, I eventually want Cell A4 to copy the format of the cell in Column F that matches the value of Cell C4.

Still following me?

For reference, my file looks like this:

enter image description here

The values of the cells in Column A are based on Sheet2.

So with a simple formula I managed to compare the values and only change Column A when there is an actual match as seen below.

=IF(C4=F:F, IF(C4<>"","x",Sheet2!A4), Sheet2!A4)

The only problem here is that it only results in a TRUE statement, if Cell C4 matches Cell F4. If it matches, let's say Cell F5, the statement will be FALSE and I need it to be TRUE (since value of C4 is indeed found in Column F.

I tried a variation using $F:$F instead of F:F, but this made no difference. Also setting a range (F2:F5) did not work.

This is where I am stuck.

As soon as I have this figured out, I can continue finding a way of copying the format of the matched cell. Feel free to reveal how I manage to do this if you already know. Will save me some headaches.

Improve this question

2 Answers 2

Reset to default
1

Try the following:

=IF(IFERROR(MATCH(C4,F:F,0),0)>0,IF(C4<>"","x",Sheet2!A4), Sheet2!A4)

Edited formula error (; --> ,)

EDIT:

Ok, so forgetting about you formula and trying to do what I think you want, which is:

Be able to copy in the list of resolvers at work into your color code scheme, and then apply that format to all tickets where this person is responsible. There has to be a way of telling, based on color, whether a ticket is assigned to a person that is not on the job (not in the list), or is not assigned to anyone. I assume you have a very limited amount of people at work, because if not, it will be very difficult to distinguish between the colors.

Being quite few people at work at one time, what you could do is to:

  1. Name your the range with people at work "workers"
  2. Name the new range "colors"
  3. For each row with a color coding, enter the name of the color, or some other way of referencing the color. I entered "Yellow" "Red", "Green", "Blue", and I added a black for "x".

  4. Selected the top cell in the tickets column, selected new conditional formatting rule, entered the following formula

    =IFERROR(INDEX(colors;MATCH($C1;workers;0));"x")="Yellow"

    Selected that this should have yellow fill, and applied to the entire Tickets column.

  5. Did the same for red, green, black, etc.

Doing this, I managed to get the following behaviour: 1. I can change the names in the name column, and conditional formatting will not change

  1. If a ticket is solved by someone in the list, the background color of TicketX is the same as the name in the name list

  2. If a ticket is not solved by anyone, the formula returns "", which is not in any of the conditional formatting rules, so it remains white

  3. If a ticket is solved by someone not at work, formula evaluates to "x", which is tied to color black in conditional formatting, so the cell turns black (could consider adding white text here to be able to see ticket number).

Now this is not that pretty, because you have to create so many rules, but as I say, if you are to distinguish between the colors, there can't be too many of them anyway.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Unfortunately this does not work for me. I need A4 to remain intact (so Sheet2!A4) whenever C4 is empty. With your formula, when C4 is empty or doesn't match any cell in column F, A4 will return #REF!
Ok. A few questions: 1. In whitch cell do you have your formula 2. What to you mean by "only change column A when there is an actual match..." Change format? Change content? 3. What is the purpose of this sheet? To see all open tickets and color code them according to resolver/submitter?
For me it works with the edited formula in that it shows x if the C is empty or there is no match in F, and if there is a match and C is not empty, it shows "x". Is that not what you ant to achieve?
Maybe that's wasn't clear. I made it show x because the x part is, I assume, where you can make A copy the format of the matching F cell
The point of the sheet is as followed. I have a list of tickets in sheet2 (column A in sheet 1). In column C will be the name of the agent resolving the ticket in column A. In column F there will be all the agents available on the current day (this is why conditional formatting based on constant names is not an option). Now I have to manually copy the format of the cell in F, containing the name of agent in cell C, to the ticketnumber in column A. Values in column A should never change, even when there is no match. If there is a match, it should get the same background color as the agent in C/
|
0

To answer the first question of seeing if C4 exists in column F, you can put the following formula in cell E4.

=IFERROR(INDEX($F$2:$F$5,MATCH(C4,$F$2:$F$5)),"")

It will put the name if it exists and have a blank field if it doesn't. Just drag this formula throughput column E (or whatever column you want).

In order to match formats and don't want to get into VBA (and assuming you don't have a lot of possibilities), you could add conditional formatting to the new column of E. With the "Classic" Style of conditional formatting, make a new rule that sets your custom format when the cell contains the specific text of "Henk". Then continue on making rules for the other possible options. After you create the conditional formatting in, say, cell E4 for example, change the "Applies to" field to apply to Sheet1!$C$2:$C$5.

Improve this answer

1 Comment

Thanks for your reply. Unfortunately this won't help, the content of column F is variable each day. This means I would have to manually make a new conditional format rule

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.