Coloured Text In Cells Formula So Excel Knows What Colour Is Used & Uses Correct Cell

Hi all,

Firstly may I thank everyone that has helped me so fat within this forum it has been fantastic! Fingers crossed someone out there will be able to help me with this question.

I have been tasked to provide a spreadsheet at work to monitor a number of applications and written applications that we receive and the number of days we respond by.

I have the following cells ‘application received’(Cell B), ‘enquiry received’(Cell C), ‘date completed’(Cell E) I then have the following formula in Cell H =NETWORKDAYS(IF(B4<>"",B4,C4),E4,$A$119:$A$158) which determines which cell has the date in (B or C) and then works out the number of days it has taken looking at cell E (the data within the $ refers to national holidays listed on the spreadsheet).

Then using conditional formatting in Cell H I use the following formula =AND(B4="",C4>0) so that if it’s the application date (Cell B) is used the text remains black and if its enquiry date (Cell C) used the answer is given in red so that you can differentiate between the two.

I then need to work out if the application or enquiry was dealt within 10days or 11days+ and I have the following cells ‘application number of days <10’ (Cell I), ‘application Number of days 11+’ (Cell J), ‘written enquiries <10’ (Cell K), ‘written enquiries 11+’ (Cell L).
I was thinking of then using =IF(H4<=10,H4,"") to populate the answer in Cell I if within 10days and =IF($H4>=11,$H4,"") if the number of days was 11+.

However I need a simple way (if there is one) for the spreadsheet to look at Cell H determine if the answer is written in black or red text, if it’s in black text it will put the number in either <10(Cell I) or 11+ (Cell J) and if its red text put the number in <10(Cell K) or 11+ (Cell L).

Can someone please help me on this (I hope I’ve explained it well)

Thanking you all in advance

P.S. I have attached a screen print of my spreadsheet for information.

Originally Posted by royUK

To best describe or illustrate your problem you would be better off attaching a dummy workbook not an image, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

RoyUK many thanks for the heads up

I have now attached a sample spreadsheet showing before and after so that people can see what i'm trying to acheive. In the 'after spreadsheet' I have just manually entered the numbers so that you can see where they would go.

I hope someone can help, thanking you in advance