Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Fill Cells based on data validation (2003 SP2)

    Good morning

    Is it possible to have a cell with Data Validation (choose from List) Mon,Tue through to Fri and once selected the Cells below follow the list, for instance, if I selected Wed could I put something in the following cells to say if I have selected Wed the Cell below is Thu, the Cell below that Fri etc.

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Fill Cells based on data validation (2003 SP2)

    You can use Excel's fill down feature to quickly fill a series of day names: if you enter Wed in a cell (whether using validation or not), then drag the fill handle in the lower right corner of the cell, Excel will fill the cells below it with Thu, Fri etc.

    If you want to use formulas it might be better to use dates and format them as ddd so that you only see the abbreviated name of the day. If you enter a date in A1, you can use the formula =A1+1 in A2 etc.

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fill Cells based on data validation (2003 SP2)

    Editted to add footnote ref 'Fill Down' feature

    Thanks Hans

    I realise that I can do that with a normal date format and I use for instance in Column B I have =IF($C$7,C7+1,"") to fill the cells down and then protect them so that only the first date selected can be used to change the rest of the dates in that time sheet.

    Because however a working week could start on say Tue I would like my department supervisors to select or typed Tue in Column A and it would fill the rest of the month down starting from that day + the date from the next column so that the 2 columns would look like |Tue|01/11/07|, next row |WED|02/11/07 or |Thu|01/11/07| next row |FRI|02/11/07|. If I format the Cells in column A to ddd and enter =A1+1 in the subsequent Cells it prodcuces a #Value error if I type in say Wed in A1

    Cheers

    Steve

    Footnote: I wanted to protect as much as the sheet as possible and in certain cells prevent the Supervisors filling down thus eliminating as many user errors as possible.
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Fill Cells based on data validation (2003 SP2)

    From the Day of the Week selected, how is the date supposed to be determined?

    I have attached an example where the day of the week selects the start of the week starting with that day in the next week...

    The sheet is protected (with no password) so only the day of the week in A1 can be selected

    Wouldn't it be easier to have them enter a date directly? or a year and week number or something like that which will gives the date (which ultimately defines the day of the week)?

    Steve
    Attached Files Attached Files

  5. #5
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fill Cells based on data validation (2003 SP2)

    Hi Steve

    Thanks as usual for a great response, what I have been trying to do is this:-

    I have 18 drivers that work remotely from the office in 3 different locations (Manchester, Edinburgh and London) each offsite location is looked after by a supervisor come driver, none of whom are particularly computer literate. I have inherited a system of receiving at the end of each month 18 pieces of paper showing what overtime each driver was claiming.

    In order to collate this and perhaps use the information gathered to better effect I thought I would use my limited Excel skills and create a workbook which the supervisors would find easy to use and email to me, I have been partly successful but am hindered, I think, by not knowing how to use any calendar controls for showing the start and end of any given month.

    Each day driver works a 45 hour week and must reach this accumulation of hours before over time is paid during Monday to Friday, therefore if the hours worked were 6,6,12,8,9 = 41 he would be paid 45 hours ignoring the fact that on Wed he worked 3 extra hours, weekends are always paid as overtime. Night drivers work slightly different hours but I had allowed for this in a list box.

    My workbook fell down because it rigidly runs from the 1st through to the 31st (Ignoring for the moment that some months have 30 days etc,) therefore making 4 blocks of 7 working days followed by the odd days at the end therefore making it impossible for me to calculate the weekday hours and then the weekend hours.

    I though that the cure would be to have the starting day in say Column A and the actual date in Column B and I could readily see where the weekends fell, however this also throws up the same kind of limitations with regards to working out whether 45 weekday hours have been completed.

    I think for the time being I will send them the workbook compiled by me based on the next working month until such time as I can develop something better over time.

    Cheers

    Steve

    This is not a 'Request' by the way, I thought I had just beeter explain what I was trying to do and why it was maybe out of kilter with normal conventions.
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Fill Cells based on data validation (2003 SP2)

    If the dates are entered, weekends and weekdays are easy to calculate.

    If you could elaborate on what you want/need and what limits you have to work with, I think we can offer some suggestions/samples. I don't completely understand what you are after so I will not even attempt a sample.

    Steve

Posting Permissions

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