Conditional formatting using a vlookup formula

[Deleted]

Hi there,

This is my first post for asking for help.  I have a spreadsheet that we use to record our outbound mail information, so it doesn't process any numbers or anything, it's merely just a record table.  It is separated into 12 worksheets - one for each month.

I have another worksheet which lists the names of clients who we are holding information on to which needs to be sent with the next outbound mail item.

I'd like to set up the conditional formatting to look up this worksheet to match the surname (we set them up individually). 

At the moment we setup each conditional formatting rule for each month - which is too timely.  It would be great for the conditional formatting to apply to all of the worksheet.

Can anyone help me out?  Please sing out if you need extra info.

thanks

Rebecca

[#2]

Hello,

it's a bit hard to visualize what you want to achieve. Can you attach a simplified example with non-confidential data? Just a few rows will be enough. Then explain the desired outcome and the logic that leads to it. 

[#3]

Hi there,

I've attached a really brief example with just one month/worksheet and a sheet for the 'client docs to be returned'. 

In the monthly worksheets, i'd like any surname that matches with a surname in the 'docs to be returned' worksheet to be highlighted, so we know to grab them and post out.

thank you

Rebecca

[#4]

Two or three more rows of data would be great, so I understand what you are trying to achieve. Also a sheet for Feb and Mar and some data that pertains to these sheets. Does the Date in the January sheet need to be matched with the date in the "clients original" sheet? I don't quite see the full extent of the connection and what rules lead to the highlight.

[#5]

Hi there,

Thanks so much for your response so far to help me.  I've updated the sheet a bit.

- the dates don't need to match, it's just for our records so we know when client info has come in

- the surname details in the client docs to return sheet and the current months sheet is what we're looking to highlight.  So when we're recording, say, today's outbound mail details, and Walker is typed in, if there is a 'Walker' surname in the 'client original docs' sheet, i'd like the name to be highlighted in the current months worksheet so I send out their docs with the letter. 

Does this help?  I haven't explained very well.

thank you

Rebecca

[#6]

I don't quite understand the bit about "Cell will turn red if client is away. Do not send mail".

How does that work?

Also, in the monthly sheets, the recipient is "R Walker" for example, but in the "client original" there is no "R Walker". If you want to match recipients, they need to show the same text in the different sheets.

In the "client original" you have columns for first and last name, but it does not really match in the case of Julie. Such details are immensely important when it comes to using formula logic to look things up automatically. 

I suggest that you insert a column in the "client originals" sheet, where you use the same client code as you do in the monthly sheets.

What happens if one client is called James Walker and another is Jezebel Walker? Would they both show as J Walker in the monthly sheets? That would be a problem. You need a unique client code in both sheets, so you can make the correct connection. 

You never really specify this, but I assume that as long as a client is listed in the "client originals" sheet, then you still have documents to be returned to that client. May I further assume that as soon as you return the docs, you will remove that client from the "client originals" sheet?? So, if a client name or client code is not found in the "client originals" it can be safely assumed that there are no docs to be returned?

You wrote "- the surname details in the client docs to return sheet and the current months sheet is what we're looking to highlight." Well, you can only highlight in one sheet, because the other sheet will be used as a lookup to figure out if a highlight needs to be applied.

I suggest that you use a helper column in the monthly sheets. In that helper column, you can have a formula that shows "documents to return" if the client is found on the "client originals" sheet. Based on that "documents to return", you can use conditional formatting for the client name or the whole row. 

See attached file.

I strongly recommend that you use Excel Tables in your monthly sheets, so that formulas and formatting get automatically applied to new rows of data. If you don't use tables, you will need to copy the formula and the conditional formatting into new rows manually.

You can hide the helper column, if it upsets your spreadsheet design. 

[#7]

Thank you - I have made the changes you suggested and it seems to be working out - very much appreciated :)