Adding colour background and number

[Btosh4]

Hi, I am hoping someone out there can help me with a formula problem, in the IAF tab, last one on sheet, I want to highlight a cell if on the Day tabs there is anything in column E, this will be text e.g. LAD, LE, E, AD, LC or NS, and in the same cell show the number that appears in column J of the day tabs, the excel sheet I'm working is to large to upload, if I delete some of the tabs all of the formulas go out of kilter. If Joe Blogs was at work on Day 1, the corresponding cell in the IAF tab should be yellow, if the supervisor monitored Joe's work on Day 1 the same IAF tab cell should remain yellow and show the number that is placed in column J on Day 1 for Joe Blogs.
P.S.
I have managed to upload an amended copy of the file, Thanks in advance for your help.

[ajryan88]

Hi,

I have adjusted the formula to show the number in column J on each worksheet (although this wasn't necessary, I have created a formula for you that can be filled down and to the right without having to rewrite it for each day). The new formula in cell C8 on the IAF worksheet is:

Please Login or Register  to view this content.

Then fill this to the right for the 4 days and down to the bottom of your table.

As for highlighting the cells which have a respective value in column E, highlight the entire table range on the IAF worksheet (C8:F55 in your attached workbook), then choose Conditional Formatting and "Use a formula to determine which cells to format", and enter the following formula:

Please Login or Register  to view this content.

Then OK your way back to the worksheet.

If you need any help either translating this to your actual workbook, or even just understanding what the hell either of these formulas are doing, please don't hesitate to ask.

I hope this helps

[Btosh4]

Thanks ajryan88 for the speedy response, will have a look through now, fingers crossed I understand it.

[Btosh4]

Thanks ajryan88, it worked a treat, greatly appreciated.

[ajryan88]

Awesome, glad to hear it!

Please don't forget to click on the * next to my post to say thanks