Dependent Lists & Timesheets...

[CloudDoctor]

Hi Everyone,

I'm currenlty creating a timesheet with 4 columns of data. Col A will
hold the Client, Col B the Team (Accounts / Marketing / Events), Col C
will be a 4 digit project number (entered manually) and Col D is the
Job code (Visit / Development / Invoice etc).

I need employees to be able to choose a client from the drop down in
A, which will then determine which list of teams they see in B. Then
make a choice in B which determines the list of jobs shown in column
D.

Employees need to account for every six minutes (!!) of their time to
allow for cross and back charging (accountants dream!).

I was able to use previous posts and Debra Dalgleish's examples to
achieve the dependent list - thanks.

The issue I'm now facing is that, so I don't annoy the employees, I
need to be able to autofill the rest of the rows (85 a day) with their
initial choice until such a time when they change clients/jobs and
make a new choice from the drop down lists. I tried adding =A3 into
A4 but the data validation wouldn't work as I think (have got brain
melt at the moment) the data validation in Debra's example
[ =IF(B3="",BusCodeList,B3) ] relies on the cells in the row being
empty...

Has anyone got any ideas? If possible I'd like to be able to achieve
this without coding.

Thanks for your time/help,

Dani

 

[Debra Dalgleish]

Assuming you have the worksheet set up with headings in row 1, data
validation in column A, and dependent data validation in columns B and D:

Enter a dummy record in row 2.
In A3, enter: =IF(A2="","",A2)
In B3, enter: =IF(B2="","",IF(A3<>A2,"",B2))
Copy those formulas down to the last row for data entry.

Clear the dummy record.
Save that as your master copy

When the employee selects values in row 2, they'll fill down the sheet
If they select a different client further down the list, it will
overwrite the formula in column A, and the adjacent cell in column B
will show as blank, so they can select a Team.

Hi Everyone,

I'm currenlty creating a timesheet with 4 columns of data. Col A will
hold the Client, Col B the Team (Accounts / Marketing / Events), Col C
will be a 4 digit project number (entered manually) and Col D is the
Job code (Visit / Development / Invoice etc).

I need employees to be able to choose a client from the drop down in
A, which will then determine which list of teams they see in B. Then
make a choice in B which determines the list of jobs shown in column
D.

Employees need to account for every six minutes (!!) of their time to
allow for cross and back charging (accountants dream!).

I was able to use previous posts and Debra Dalgleish's examples to
achieve the dependent list - thanks.

The issue I'm now facing is that, so I don't annoy the employees, I
need to be able to autofill the rest of the rows (85 a day) with their
initial choice until such a time when they change clients/jobs and
make a new choice from the drop down lists. I tried adding =A3 into
A4 but the data validation wouldn't work as I think (have got brain
melt at the moment) the data validation in Debra's example
[ =IF(B3="",BusCodeList,B3) ] relies on the cells in the row being
empty...

Has anyone got any ideas? If possible I'd like to be able to achieve
this without coding.

Thanks for your time/help,

Dani

 

[CloudDoctor]

Thanks Debra,

I've added the formula but is doesn't seem to have changed anything -
I still can only choose the client already chosen (ie the drop down is
restricted - it doesn't show the full list).

This is the 3rd or 4th attempt at a solution for me! My original task
was to create a set of drop downs that were dependent on the original
and second choices...I had been able to make the third choice
dependent on the first choice using a combination of named ranges on
another sheet and the OFFSET, MATCH and COUNTA functions:

=OFFSET(JobCode_top,1,MATCH(BusinessCode,BusinessCode_List,0)-1,COUNTA
(OFFSET(JobCode_rows,0,MATCH(BusinessCode,BusinessCode_List,
0)-1))-2,1)

The next step was making the third drop down dependent on the 2
original choices - I just haven't been able to make the leap from 2 to
3 choices. Unfortunately very similar choices are involved in the
second list but the thrid list is different; ie choose a client,
choose a team and then the jobs are different depending on which
client you have chosen. I thought I'd found the perfect solution from
your website (which is sooo helpful!) but I don't want the duplication
in the lists as I have about 11 teams and that list isn't going to get
any smaller!

I want to use this OFFSET / MATCH solution as it will let our finance
team add clients, teams and jobs easily without getting IT involved
(too much!).

Is there a solution? Or will I have to resort to coding? I can send a
sample of what I'm working on if that makes it easier! Understand if
my ramblings have not made sense!


Thanks again,

CloudDoctor

Assuming you have the worksheet set up with headings in row 1, data
validation in column A, and dependent data validation in columns B and D:

Enter a dummy record in row 2.
In A3, enter: =IF(A2="","",A2)
In B3, enter: =IF(B2="","",IF(A3<>A2,"",B2))
Copy those formulas down to the last row for data entry.

Clear the dummy record.
Save that as your master copy

When the employee selects values in row 2, they'll fill down the sheet
If they select a different client further down the list, it will
overwrite the formula in column A, and the adjacent cell in column B
will show as blank, so they can select a Team.

Hi Everyone,

I'm currenlty creating a timesheet with 4 columns of data. Col A will
hold the Client, Col B the Team (Accounts / Marketing / Events), Col C
will be a 4 digit project number (entered manually) and Col D is the
Job code (Visit / Development / Invoice etc).

I need employees to be able to choose a client from the drop down in
A, which will then determine which list of teams they see in B. Then
make a choice in B which determines the list of jobs shown in column
D.

Employees need to account for every six minutes (!!) of their time to
allow for cross and back charging (accountants dream!).

I was able to use previous posts and Debra Dalgleish's examples to
achieve the dependent list - thanks.

The issue I'm now facing is that, so I don't annoy the employees, I
need to be able to autofill the rest of the rows (85 a day) with their
initial choice until such a time when they change clients/jobs and
make a new choice from the drop down lists. I tried adding =A3 into
A4 but the data validation wouldn't work as I think (have got brain
melt at the moment) the data validation in Debra's example
[ =IF(B3="",BusCodeList,B3) ] relies on the cells in the row being
empty...

Has anyone got any ideas? If possible I'd like to be able to achieve
this without coding.

Thanks for your time/help,

Dani

--
Debra Dalgleish
Contextureshttp://www.contextures.com/tiptech.html- Hide quoted text -

- Show quoted text -

 

[Debra Dalgleish]

Okay, if you want to email your sample file, I'll take a look, and will
post any questions or comments here.

Thanks Debra,

I've added the formula but is doesn't seem to have changed anything -
I still can only choose the client already chosen (ie the drop down is
restricted - it doesn't show the full list).

This is the 3rd or 4th attempt at a solution for me! My original task
was to create a set of drop downs that were dependent on the original
and second choices...I had been able to make the third choice
dependent on the first choice using a combination of named ranges on
another sheet and the OFFSET, MATCH and COUNTA functions:

=OFFSET(JobCode_top,1,MATCH(BusinessCode,BusinessCode_List,0)-1,COUNTA
(OFFSET(JobCode_rows,0,MATCH(BusinessCode,BusinessCode_List,
0)-1))-2,1)

The next step was making the third drop down dependent on the 2
original choices - I just haven't been able to make the leap from 2 to
3 choices. Unfortunately very similar choices are involved in the
second list but the thrid list is different; ie choose a client,
choose a team and then the jobs are different depending on which
client you have chosen. I thought I'd found the perfect solution from
your website (which is sooo helpful!) but I don't want the duplication
in the lists as I have about 11 teams and that list isn't going to get
any smaller!

I want to use this OFFSET / MATCH solution as it will let our finance
team add clients, teams and jobs easily without getting IT involved
(too much!).

Is there a solution? Or will I have to resort to coding? I can send a
sample of what I'm working on if that makes it easier! Understand if
my ramblings have not made sense!


Thanks again,

CloudDoctor

Assuming you have the worksheet set up with headings in row 1, data
validation in column A, and dependent data validation in columns B and D:

Enter a dummy record in row 2.
In A3, enter: =IF(A2="","",A2)
In B3, enter: =IF(B2="","",IF(A3<>A2,"",B2))
Copy those formulas down to the last row for data entry.

Clear the dummy record.
Save that as your master copy

When the employee selects values in row 2, they'll fill down the sheet
If they select a different client further down the list, it will
overwrite the formula in column A, and the adjacent cell in column B
will show as blank, so they can select a Team.

Hi Everyone,

I'm currenlty creating a timesheet with 4 columns of data. Col A will
hold the Client, Col B the Team (Accounts / Marketing / Events), Col C
will be a 4 digit project number (entered manually) and Col D is the
Job code (Visit / Development / Invoice etc).

I need employees to be able to choose a client from the drop down in
A, which will then determine which list of teams they see in B. Then
make a choice in B which determines the list of jobs shown in column
D.

Employees need to account for every six minutes (!!) of their time to
allow for cross and back charging (accountants dream!).

I was able to use previous posts and Debra Dalgleish's examples to
achieve the dependent list - thanks.

The issue I'm now facing is that, so I don't annoy the employees, I
need to be able to autofill the rest of the rows (85 a day) with their
initial choice until such a time when they change clients/jobs and
make a new choice from the drop down lists. I tried adding =A3 into
A4 but the data validation wouldn't work as I think (have got brain
melt at the moment) the data validation in Debra's example
[ =IF(B3="",BusCodeList,B3) ] relies on the cells in the row being
empty...

Has anyone got any ideas? If possible I'd like to be able to achieve
this without coding.

Thanks for your time/help,

Dani

--
Debra Dalgleish
Contextureshttp://www.contextures.com/tiptech.html- Hide quoted text -

- Show quoted text -