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

    Data Validation: How to use the results of (2003 SP2)

    Editted: it helps if you attach the file to which you are referring :-)

    Good morning

    I am trying to use Data Validation to affect cells in other worksheets, in the attached example for January I have used Validation to select a Name, Start Date,End Date and Reason for Absence how can I now translate this information into the January worksheet (I know this is not a correct type of formula but in laymens terms) Match A4,January!$C$5:$C$7(name) and B4 and B5 January!$D$4:$AH$4 and insert whatever is in D4 into the appropraite cells, in this example a T would be inserted into cells E5 to H5.

    Hope this makes sense

    Cheers

    Steve
    Attached Files Attached Files
    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: Data Validation: How to use the results of (2003 SP2)

    You would need VBA code to transfer the data to the January sheet. Data | Validation only presents instructions, error messages and dropdown lists (if set up for that). It can't be used to transfer data to other cells.

    Instead, do the following:
    1) Activate the sheet for the appropriate month.
    2) Find the row for the employee.
    3) Click on the first day of absence, then shift+click on the last day.
    It is also possible to drag from the first to the last day of absence.
    4) Type the letter code and press Ctrl+Enter to fill all selected cells.

  3. #3
    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: Data Validation: How to use the results of (2003 SP2)

    <P ID="edit" class=small>(Edited by sdckapr on 02-Nov-07 07:31. Attached modification. Forgot to put Validation in Absenses sheet)</P>Does this do what you want?

    I created a new Sheet and way to enter the absences (without VB you would need to list each absence (see "Absences" sheet). The Year goes in A1, then you enter the name (could use validation if desired) and the month, day and the Reason code. It calculates a name/date for "lookup" (column may be hidden if desired).

    The formulas in the month sheets are:
    =IF(ISERROR(MATCH($C5&TEXT($C$3+D$4-1,"mm/dd/yyyy"),Absences!$E:$E,0)),"",INDEX(Absences!$D:$D, MATCH($C5&TEXT($C$3+D$4-1,"mm/dd/yyyy"),Absences!$E:$E,0)))

    It calculates the name/month combo to lookup using the date in C3 and the Day of the week in the 4th row (subtracting 1 since the date starts with 1) and then looks it up in Col E of the Absences sheet. If it is an error (ie not found) it puts a "null" (nothing in the cell) if found it uses index to get the reason code).

    Steve
    Attached Files Attached Files

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

    Re: Data Validation: How to use the results of (2003 SP2)

    Thanks for the tip Hans that works well, I also found out by experimenting with this that if somebody takes 10 days and I want to omit the weekend I can click on say the 1st, hold shift, click on the 5th, hold CTRL, click on the 8th, back to shift and click on the 12th, enter value hit return, job down, sounds complicate but its really easy

    Cheers

    Steve
    Cheers

    Steve

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

  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: Data Validation: How to use the results of (2003 SP2)

    Wow, fantastic, I will be playing all weekend now, at a quick glance I can see in the formula that the last bit Absences!$E refers back to the column that contains the formulas in that worksheet but I can't fathom out $E$E?

    Thanks again

    Steve
    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: Data Validation: How to use the results of (2003 SP2)

    It is $E:$E (not $E$E) and it just refers to the entire column.

    You can, of course, just use the desired range $E$1:$E:$100 (or whatever). You would do the same number of rows for the MATCH and the INDEX.

    Steve

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

    Re: Data Validation: How to use the results of (2003 SP2)

    Hi Steve

    Thanks for all your efforts with this but now that I have built my months and employee list around it there are some possible 31,000 cells and for some reason it seems to clunk and grind over calculating the indivudual combination of cells (on the task bar it keeps flashing calculating cells) and it can take 2 - 4 minutes to update 1 holiday period. I think this was obviousely bad design and planning on my part.

    However searching around and trying to tweak things I think I have a non VBA (I am trying honestly to learn how to do it!!) but I think I will need some of your and the other loungers expertise to tweak it.

    Thanks again

    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
  •