Can these 2 formulas be merged in to 1

[Toonies]

Hi and thank you for taking time ou to look at my question


I have the following 2 formulas which work fine on their own

Excel Workbook
	A	B	C	D	E	F	G	H
5
6	COLLEAGUE NAME		DEPARTMENT		SHIFT			06:00
7								|
8								06:30
9
10	ANDREA ALLEN				8:00	17:00		REVERSE PICK
11								0
Master Schedule

I am wondering is there any way to merge them into one

I look forward to any suggestions or pointers if it is possible.

 

[jasonb75]

What is the purpose of trying to merge them, the 2 formula seem unrelated.

A totally wild guess.

=IF(AND($E10<=H$6,$F10>H$6),IF(ISERROR(INDEX(Task!$A$1:$A$20,MATCH($A10,'SAT DRAFT'!G$8:G$27,0))),"",INDEX(Task!$A$1:$A$20,MATCH($A10,'SAT DRAFT'!G$8:G$27,0))),"")

 

[Toonies]

Hi it is an daily task schedule

The 1st formula will display tasks that have been allocated from sheet "SAT DRAFT"

the 2nd formula will hilight the cells when it falls within a certain time range.

Just wondering if I could combine them

 

[Toonies]

hi it just makes the cell go blank

 

[jasonb75]

what do you mean "highlight"? that would suggest conditional formatting, yet both formula are in the worksheet.

 

[Toonies]

Yes Conditional formatting is used

Cell H10

SUMPRODUCT(COUNTIF(H10,"*"&Task&"*"))>0

this highlights the cell "Blue"

Cell H11

If equal to 0

Highlight cell & Text "White"

If equal to 1

Highlight cell & Text "Blue"

 

[jasonb75]

Try

AND($E10<=H$6,$F10>H$6,SUMPRODUCT(COUNTIF(H10,"*"&Task&"*"))>0)

as your conditional format rule for H10.

 

[Toonies]

Hi this indeed does pont me in the right direction as it highlights the cell when between a certain time frame.

what I am looking for if possible is to highlight the cells within the time frame whilst still showing any allocated tasks from sheet "SAT DRAFT"

 

[jasonb75]

With your original formula in H10, it should do exactly the same as what you have already.

 

[Toonies]

Hi it does but only for specific cells that have allocated tasks in.

Would it be possible to highlight the full time frame say for 6:00 to 8:00 and any any allocated tasks highlighted the same color.

The cells outside that range would have no color fill and any allocated tasks would be shown a different color.

I hope that I have explained it correctly