lookup formula

[sabi.kiss]

Hi there,

Hopefully someone could help me with this.
I'm trying to do a leave planner for each one of our sites.
In the top row I've got the full financial year, day-by-day. On another sheet I've got column A with employee names, column B with leave start date and column C leave finish date. I am looking for a formula, which in planner sheet, in the second row, puts a X under the actual date, if that date is between start and finish date on the Data sheet.
That way, instead of highlighting days for each employee, I could do a automated sheet.
Would this be possible?

Thanks,
Sabi

 

[sabi.kiss]

All good gents, I just added another Method with Application.CalculateFull and it work perfectly.
Thanks for all your help.
Amazing people.

Sabi

 

[sabi.kiss]

Back again. Only one issue. Obviously I've got quite a few employees and spread out over a year, is quite a bit of data to calculate.
I've only put in 30 and the refresh is absolutely slow.
Is there a better way of doing it?

Sabi

 

[njimack]

Insert the following at the start of the code:

Application.volatile

The number of entries will obviously affect calculation but I wouldn't expect it to be noticeable until you have several hundred rows of data.

 

[sabi.kiss]

OK..getting there slowly.
What if I would like to automate the whole sheet, and insert Name, Start date and Finish date on the "DATA" sheet, and the rest gets filled out automatically. Thanks to Neil, we sorted out the Date side of it, what about the Name. I couldn't figure out how to modifi Neil's VBA to reflect on my reqest.
So, column A in "PLANNER" sheet will get the names automatically from "DATA" sheet with the leave dates requested by each employee, thats set up in between Start and Finish Date. If there's a duplicate Name or more entries for the same name, on "PLANNER" sheet the particular name would appear only once, with the different dates taken as leave.

Hope I didn't make it too confusing.

Thanks heaps guys.

Sabi

 

[sabi.kiss]

Njimack,

Could you help changing this a little bit. I've had been looking around for quite a while and could not find anything like it.
I've got 2 sheets, DATA & WORK.
DATA sheet has got 5 columns: STARTDATE, STARTTIME, FINISHDATE, FINISHTIME & EMPLOYEENAME
WORK sheet has got a DATE cell (this would be the criteria for the sheet), then lets say row 8 will have the date & row 9 will have the times 08:00 to midnight, for each day. the WORK sheet would cover a full week (7 days), 08:00 to midnight for each day (17 columns each day). Row 10 will have the X in the cells, conditional formatted as the previous work you did with the dates per employee. Each one of the 17 cells would be 1 hour coverage.
Do you reckon this would be possible?

Thanks