Printable View
Hi,
I have 2 sheets in a excel file, in sheet1 I have start date and end date of a given task and in sheet2, I have dates from C6 to EY6 (1 Aug to 31dec) and in column B the task name. I need help in writing macro to merge the cells in sheet1 basis the date range and task from sheet2. If any expert can help me with his/her email I can send the sample file. Also is it possible to exclude holidays and weekends? Attaching the file.
Thanks
RajeshAttachment 83
Could you attach a sample of the expected output? How do you want the cells to be merged? Yes, weekends could be excluded, but you need to tell how you want the output to look like, given a sample of the input also
Ah, solution already suggested by Admin :)
cheers
Hi Rajesh,
Welcome to ExcelFox !!
It can be done through conditional formatting.
Define ranges:
EndDate
Refers to: =Sheet1!$G$4:$G$8
Holidays
Refers to: =Sheet1!$N$4:$N$23
SalesPersons
Refers to: =Sheet1!$C$4:$C$8
StartDate
Refers to: =Sheet1!$F$4:$F$8
Select C4:EY9 on sheet2
In Conditional formatting
Rule1:
=OR(WEEKDAY(C$3,2)>5,ISNUMBER(MATCH(C$3,Holidays,0 )))
Rule2:
=AND(ISNUMBER(MATCH($B4,SalesPersons,0)),C$3>=INDE X(StartDate,MATCH($B4,SalesPersons,0)),C$3<=INDEX( EndDate,MATCH($B4,SalesPersons,0)),WEEKDAY(C$3,2)< 6,ISNA(MATCH(C$3,Holidays,0)))
Format the cells accordingly.
Note: I have define a range for Holidays.
PFA.
If you prefer VBA rather than CF, let me know.
Great, Thanks, the only question is how to delete the data (dots) from highlighted cells....actually each cell is refering 1 day and i wants to calculate the number of days worked by the sales man , by this way i can calculate the weekly utilization. For example to calculate the number of day worked in aug 1st week I am planing to use COUNTIF(C4:I4,"<>.") formula , this will count the blank cells which will be the number of days for me. If you can suggest a better way to do this...that will be a great great help. The final output i want the number os days woked by a sales man in a week.
Thanks
Rajesh
Here's one way to do it
=IFERROR(SUMPRODUCT((C$3:I$3>=INDEX(Sheet1!$F$4:$F $8,MATCH($B12,SalesPersons,0)))*(C$3:I$3<=INDEX(Sh eet1!$G$4:$G$8,MATCH($B12,SalesPersons,0)))*(WEEKD AY(C$3:I$3,2)<6)*ISNA(MATCH(C$3:I$3,Holidays,0))), 0)
WOW...this is awesome.....it is work for me...Let me implement this and surely share the feedback with you :).
Thanks a ton
Rajesh
Attachment 88Attachment 89
Hi, I tried this and it worked great for the cell highlighting portion..but i was failed to implement the formula to calculate the days :(... that may be because i was trying to refelect the days in a diffrent sheet. I am attaching both the file i worked on. The Utilization file is the one i wants to refelect the days and sample allocation is the plotter which is working fine....also is there s way that if some one works on sat or sunday that can also be refelect in the plotter.
Thanks in anticipation.
Rajesh
The utilization file does not have the start date and end date for each member
Use this in D7 of utilization sheet
=SUMPRODUCT(('[Sample Allocation.xlsx]Plotter'!$K$4:$Q$4>=INDEX('[Sample Allocation.xlsx]Plan'!$H$4:$H$9,MATCH($B7,'[Sample Allocation.xlsx]Plan'!$G$4:$G$9,0)))*('[Sample Allocation.xlsx]Plotter'!$K$4:$Q$4<=INDEX('[Sample Allocation.xlsx]Plan'!$I$4:$I$9,MATCH($B7,'[Sample Allocation.xlsx]Plan'!$G$4:$G$9,0)))*ISNA(MATCH('[Sample Allocation.xlsx]Plotter'!$K$4:$Q$4,'[Sample Allocation.xlsx]Plan'!$R$3:$R$4,0)))
Thanks , This worked for me.