1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

trouble with data validation formulas

Discussion in 'Business Applications' started by rix37, Nov 13, 2011.

Thread Status:
Not open for further replies.
  1. rix37

    rix37 Thread Starter

    Joined:
    Nov 13, 2011
    Messages:
    2
    I have been trying to sort this out for days and each time I think I've cracked it I hit on another problem.

    I am trying to produce a rota for all the members on our team at work which cuts out the need for annotating printed copies by hand with everything self-generating within the spreadsheet.

    So far I have the staff names in a list for the main part of the rota where you select from a dropdown. That works perfectly.

    The problem is the other part of the rota. I can select from a dropdown to indicate when a staff member has annual leave or a training day but it is the days off in lieu of weekends that are causing me all the grief.

    I am trying to reach a point where when you select a staff member with the day off option (e.g. Joe Bloggs DO), the dependent dropdown after it has the correct weekend days combined with the staff member's name (the weekend preceding the Monday of that week's rota and the weekend following the Friday of that week's rota) i.e. for week beginning Monday 6th December the dropdown would say "Joe Bloggs DO four times respectively followed by the dates 4/12, 5/12, 11/12 and 12/12.

    Each week of the rota has the Monday date in the upper left cell with a relative row reference which adds seven each time you copy and paste in the same relative position. The dates for that week simply add 1 to the cell to their left so with each copy of the rota the dates effectively prefill automatically. That works fine.

    So far I have got a worksheet which has all the weekend dates listed against the date for each Monday of the weekly rota which has provided the source data for some of what I have tried to do.

    This then feeds into a grid on the right hand side of the rota spreadsheet with a formula which concatenates the result of an index/match function with each staff member's name. I would like this grid to be dynamic but that is a separate issue for now. I think I know how to do that having read stuff on contextures.

    I have then tried creating a named range for each staff member (which would be the source for the second dependent dropdown following Joe Bloggs DO) and then used index and match to identify the row (determined by the Monday date) and the column (determined by the staff member's name) but as each Monday date has four dates associated with it, I cannot get the row function to do what I want despite following instructions I have found on line for creating an index formula which returns multiple values for a unique lookup value.

    Any ideas?
     
  2. rix37

    rix37 Thread Starter

    Joined:
    Nov 13, 2011
    Messages:
    2
    I'm getting closer to a solution - to make it easier, I have named the header row with the staff member's names, and named the entire area where the concatenations of staff name/dates are stored. This will eventually be dynamic.

    So that each of the four weekend dates for each Monday are unique, I have added a column to the left which contains the column numbers referencing the table on the other sheet with the weekend dates in it. This makes it much easier both for copying and enables me to concatenate the column number with the Monday date so that each row has a unique reference.

    The problem I am left with is how to get the INDEX/MATCH to work to pick out the relevant dates for each week of the rota. I can get it to pick out one but I have four rows with dates in and only one with the staff member's name so it would throw out the relative referencing (I think)
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1026762

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice