Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Format Cells Based on Given Input

  1. #1
    Member
    Join Date
    Aug 2011
    Posts
    92
    Rep Power
    14

    Format Cells Based on Given Input

    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
    RajeshCell_Merge_Sample.xlsx

  2. #2
    Grand Master
    Join Date
    Apr 2011
    Posts
    22
    Rep Power
    10
    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

  3. #3
    Grand Master
    Join Date
    Apr 2011
    Posts
    22
    Rep Power
    10
    Ah, solution already suggested by Admin

    cheers

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    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.
    Attached Files Attached Files
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  5. #5
    Member
    Join Date
    Aug 2011
    Posts
    92
    Rep Power
    14
    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

  6. #6
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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)
    Attached Files Attached Files
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  7. #7
    Member
    Join Date
    Aug 2011
    Posts
    92
    Rep Power
    14
    WOW...this is awesome.....it is work for me...Let me implement this and surely share the feedback with you .

    Thanks a ton
    Rajesh

  8. #8
    Member
    Join Date
    Aug 2011
    Posts
    92
    Rep Power
    14
    Sample Allocation.xlsxUtilization.xlsx

    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

  9. #9
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    The utilization file does not have the start date and end date for each member
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  10. #10
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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)))
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

Similar Threads

  1. Replies: 4
    Last Post: 02-22-2013, 02:24 AM
  2. Filling A Number Of Cells Based On Numerical Value
    By Ryan_Bernal in forum Excel Help
    Replies: 5
    Last Post: 02-13-2013, 09:44 PM
  3. Replies: 2
    Last Post: 01-24-2013, 09:03 PM
  4. Replies: 1
    Last Post: 12-04-2012, 08:56 AM
  5. Conditional Format Based On Percentage Variance
    By srizki in forum Excel Help
    Replies: 3
    Last Post: 10-09-2012, 03:28 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •