Dependent list doesn't work

Hi guys. I could use a bit of help here; been banging my head against the wall for the whole day trying to get this to work :-)


I have a named list defined called ActivityPaid with the following entries:

Documentation
On site
Pre staging
Project management
Training


Then I have a named list defined for each Item in the previous list:

Documentation
On site
Pre staging
Project management
Training

I will base this example on the list named Documentation but the principle is the same for all 5 lists. The list Documentation is a range of cells in a single column where only cells with data should be displayed. I've defined the list as follows and it's working as it should:

=OFFSET(Sheet2!$B$11,,,COUNTA(Sheet2!$B$11:$B$60),1)


Then in another sheet I have configured validation (list) for cells in column E (Type of work) as follows:
=ActivityPaid

The contents of the cell in column F (Task) should be dependent on what's selected in the cell in column E. I've configured validation for column F as follows:

=INDIRECT(E6)


Excel comes back saying the source evaluates to an error and as such my list (Documentation) is not displayed in Column F.

As soon as I change my named list Documentation to a normal range of cells (=Sheet2!$B$223:$B$272) it works fine.


Any help would be greatly appreciated.

Sp00k

Originally Posted by Sp00k

Hi guys. I could use a bit of help here; been banging my head against the wall for the whole day trying to get this to work :-)


I have a named list defined called ActivityPaid with the following entries:

Documentation
On site
Pre staging
Project management
Training


Then I have a named list defined for each Item in the previous list:

Documentation
On site
Pre staging
Project management
Training

I will base this example on the list named Documentation but the principle is the same for all 5 lists. The list Documentation is a range of cells in a single column where only cells with data should be displayed. I've defined the list as follows and it's working as it should:

=OFFSET(Sheet2!$B$11,,,COUNTA(Sheet2!$B$11:$B$60),1)


Then in another sheet I have configured validation (list) for cells in column E (Type of work) as follows:
=ActivityPaid

The contents of the cell in column F (Task) should be dependent on what's selected in the cell in column E. I've configured validation for column F as follows:

=INDIRECT(E6)


Excel comes back saying the source evaluates to an error and as such my list (Documentation) is not displayed in Column F.

As soon as I change my named list Documentation to a normal range of cells (=Sheet2!$B$223:$B$272) it works fine.


Any help would be greatly appreciated.

Sp00k

Here's one way you may fix it:

Documentation: =Sheet2!$B$223:$B$272

Then make the validation: =OFFSET(INDIRECT(E6),,,COUNTA(INDIRECT(E6)),1)

HTH,
Scott