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

    Holiday Calendar Revisited (2003 SP2)

    Good morning

    I have a holiday form (thanks Steve for all of the input) (see post starting at <post#=678167>post 678167</post#> ) that because the sheer number of cells that have to be crunched through take some time to update (I have each month on 1 sheet, 2008, so that January is 31 cells across and 70 cells down, a coouple of gaps, then February etc.)

    To give you an idea of the sheets structure

    Data is entered through 4 fields being Name (validation cell from a list), From (dd/mm/yy), To (dd/mm/yy) and Leave Type (validation cell from a list) that run down the whole sheet from $A$6:$D$780 (the column headings are in the row above) which when filled in allocates the days in the calendar.

    Whilst experimenting I have found that the whole process is speeded up a lot if I choose 'Data' and 'Form' using my row headings Name,From,To,Leave Type, however

    1. The form only appears when invoked through 'Data' and 'Form' and I would prefer it to appear perhaps on a different sheet automatically
    2. The name and the leave types on the original form are validated to decrease user error, can I somehow use this validation in the Data Form
    3. The from and to fields are formatted as dd/mm/yy can I make the Data Form be formatted the same.

    Sorry if its complicated but the water has just reached my neck!! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    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: Holiday Calendar Revisited (2003 SP2)

    Data | Form is a rather old part of Excel, it hasn't been updated for years. None of the things you mention is possible with the built-in data form.
    You might try John Walkenbach's free add-in J-Walk Enhanced Data Form v2, it provides extra features such as dropdown lists.

  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: Holiday Calendar Revisited (2003 SP2)

    Thank you Hans, a good reference

    If on a dummy sheet I set up a list of names and call the range staff, and then another list and call it Absence and use them in my column headers (so that I have Name - From - To - Absence then select the 4 column headers and open the J Walk Add in it is fine and Name and Absences are combos containing the dummy data that I put in those ranges.

    I have now gone to try this on my live worksheet but I am having a problem, I have changed the 4 column headers where I would like the Data Form to add the data to be the same as the named ranges but when I invoke the Add in it selects every single column header on that worksheet (65 in total) instead of the 4 that I require, nothing I do seems to stop it.

    Another quick thing, if I email this workbook to anybody will the add in go with it?

    Thanks as usual

    Steve
    Cheers

    Steve

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

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

    Re: Holiday Calendar Revisited (2003 SP2)

    The Enhanced Data Form uses the entire contiguous area as "database". You cannot change that.

    The add-in is not stored in the workbook. Each user will have to install it on their own PC.

  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: Holiday Calendar Revisited (2003 SP2)

    Thanks Hans

    I will have to work on another alternative, I have been trying to play around at home with user forms, perhaps the answer will lay in that direction for me.

    Cheers

    Steve
    Cheers

    Steve

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

Posting Permissions

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