Gannt Chart not executing with cells containing vlookup data

[KarbonEnk]

Excel v.2010 | OS Windows 7

Problem: The gannt chart being produced with conditional formatting doesn't execute with cells that have vlook up data. I'm comparing a start time and an End time. The conditional formatting works when you type in the time directly into the cell.

Files:
Description File - This is the file that I want all the data to be displayed in (In the leader's box, the information is being pulled from vlookup
In the Cashwrap box, the times are typed in directly to show that the gannt chart works)

Lookup Value - This file contains the names that I am looking up

Lookup Data - This file contains the table that I am pulling into the description file.

I hope my issue is clear but if not, let me know and I can elaborate further. Thanks in advance Excel Team.

Google

 

[Akashwani]

Hi and welcome to MrExcel.

I guess the reason why you haven't received a reply is because you haven't provided and sample data, the formula you are using for your Conditional Formatting or your Vlookup formula. I'm sure if you provide this information then someone will provide you with the answer you require.

Ak

 

[easy2understandexcel]

Are automatic cell calculations enabled or disabled?

 

[KarbonEnk]

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Smith, John[/TD]
[TD]7:00 am[/TD]
[TD]12:00pm[/TD]
[TD]8:00am[/TD]
[TD]9:00am - 10:00am[/TD]
[/TR]
</tbody>[/TABLE]


Above: Look up data

[TABLE="class: grid, width: 90, align: left"]
<tbody>[TR]
[TD]Smith, John[/TD]
[/TR]
</tbody>[/TABLE]


Above: Value looking up

[TABLE="width: 400, align: left"]
<tbody>[TR]
[TD]name
[/TD]
[TD]start
[/TD]
[TD]end
[/TD]
[TD]break
[/TD]
[TD]lunch
[/TD]
[TD]7am
[/TD]
[TD]7:15am
[/TD]
[TD]7:30am
[/TD]
[TD]7:45am
[/TD]
[TD]8:00am
[/TD]
[TD]8:15am
[/TD]
[TD]8:30am
[/TD]
[TD]8:45am
[/TD]
[TD]9:00am
[/TD]
[TD]9:15am
[/TD]
[TD]9:30am
[/TD]
[TD]9:45am
[/TD]
[TD]10:00am
[/TD]
[TD]10:15am
[/TD]
[TD]10:30am
[/TD]
[/TR]
[TR]
[TD]Smith, John
[/TD]
[TD]7:00 am
[/TD]
[TD]12:00pm
[/TD]
[TD]8:00am
[/TD]
[TD]9:00am -10:00 am
[/TD]
[TD="align: center"]x
[/TD]
[TD="align: center"]x
[/TD]
[TD="align: center"]x
[/TD]
[TD="align: center"]x
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]x
[/TD]
[TD="align: center"]x
[/TD]
[TD="align: center"]x
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]x
[/TD]
[TD="align: center"]x
[/TD]
[/TR]
</tbody>[/TABLE]





Vlookup Formula:

=IFERROR(VLOOKUP([Athlete_Roster.xlsx]Departments'!$B12,'[1-06-13.xlsx]Sheet1'!$A$7:$BQ$94,1,FALSE),"")

Formating Formula:

=AND(F$21>=$B22,G$21<=$C22)

I can get the gannt chart to work by adding a zero at the end of the start time field but I don't know how to display the lunch field in the gannt chart

 

[Akashwani]

Hi,

I'm not sure if this is the solution you require.

Sample data.....

Excel Workbook
	A	B	C	D	E	F
1	Name	Start	End	Break	Lunch
2	Smith. John	07:00	12:00	08:00	09:00 am - 10:00 am
3	Smith, Jon	07:00	12:00	08:00	09:00 am - 10:00 am
4	Smith John	07:00	12:00	08:00	09:00 am - 10:00 am
5	Smith, John	07:00	12:00	08:00	09:00 am - 10:00 am
6	Smith. Jon	07:00	12:00	08:00	09:00 am - 10:00 am
7
Sheet1

Sample Vlookup result.....

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	Name	Start	End	Break	Lunch	7:00	7:15	7:30	7:45	8:00	8:15	8:30	8:45	9:00	9:15	9:30	9:45	10:00	10:15	10:30	10:45	11:00	11:15	11:30	11:45	12:00
2	Smith, John	7:00 AM	12:00 PM	8:00 AM	09:00 am - 10:00 am	x	x	x	x		x	x	x						x	x	x	x	x	x	x	x
3
Sheet2

The formula in B2 needs copying across to E2 and the formula in F2 needs copying across.
The numbers in F1:Z1 have been entered as time using the formatting of h:mm.
I have entered the Lunch time as....
09:00 am - 10:00 am
instead of.....
9:00am - 10:00am

Conditional Formatting formula....
=F2=""

I hope this helps.

Ak

 

[KarbonEnk]

That works perfectly! Thank you so much for taking the time to help me find a solution.

 

[Akashwani]

Hi,

I'm pleased it worked for you, thanks for the feedback.

Ak