Narrowed down data validation from previous selection

[G Wiz]

I am looking to use some predefined data to be able to select based on another cells selection.

So the document I have the following headings on columns.

Stage

Task Type

Task

I have under Stage data validation allowing me to select a Stage.

I have a tab for Task Type which lists all the Task Types across a single row, with the individual Tasks below running down the columns.

Back to my main tab Task Type has data validation to allow me select one of the Task Types from the other tab.

Essentially what I want to do is when selecting a particular Task Type, only the Tasks that are under the Task Type are selectable in the Task column.

Is this possible with a formula of some sort, or am I going to need to build the spreadsheet a bit differently?

Thanks

 

[Bosquedeguate]

TaskTypes (TT1 - TT3)
And Tasks for each TaskType

Book3.xlsx
	A	B	C
2	TT1	TT2	TT3
3	Run	Wake	Brush
4	Walk	Eat	Floss
5	Stand	Work	WaterPik
Sheet1

Use the INDIRECT function for data validation lists for your tasks
the argument for the INDIRECT function is the value from the Cell that contains the selected Task Type

Note: when I created my Tasks Lists ("TT1", "TT2" and "TT3") the named range manager tacked on a "_" character at the end of the named range name. That is why I had to concatenate the underscore character to make my example work.

Book3.xlsx
	A	B	C
10	Select Task Type		TT1
11	Select Task		Run
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
C10	List	=TaskTypes
C11	List	=INDIRECT(C10&"_")

So for your example
The data validation lists used could be
=StageList for the first column
=TaskTypes for the 2nd column
=INDIRECT(B2) - assuming Cell A2 = Stage; Cell B2 = TaskType; Cell = C2 (will be your tasks for the selected TaskType)
check your named range manager for the Excel assigned Name (it may include a trailing underscore character "_"

 

[G Wiz]

Many thanks.

I'll try this later if I can work out what you've suggested!

 

[G Wiz]

Stumped. I'm not really skilled enough to replicate this in my spreadsheet.

 

[G Wiz]

Note: when I created my Tasks Lists ("TT1", "TT2" and "TT3") the named range manager tacked on a "_" character at the end of the named range name. That is why I had to concatenate the underscore character to make my example work.

Is this adding the "" to the title or every individual task?

 

[G Wiz]

I've stripped my version down.

See below images.

Note, separate tabs for relevant data as I do not want this in the main sheet.

Note:

I am showing formulas on each tab, well the only one that has them is C2 in Time Report TEST.

 

[Bosquedeguate]

Is this adding the "" to the title or every individual task?

I don't think so, I just noticed that the underline character was tacked onto my 3 named lists. If you do not see it in the named list manager then you don't need it.

 

[Bosquedeguate]

I've stripped my version down.

See below images.

Note, separate tabs for relevant data as I do not want this in the main sheet.

View attachment 96636


View attachment 96637

View attachment 96638

Note:

I am showing formulas on each tab, well the only one that has them is C2 in Time Report TEST.

I have a dedicated worksheet for my Lists, Lookup Tables, etc. (and usually make all of these named ranges - see snapshot from the Named Range manager)

DynamicDataValidationLists.xlsm
	A	B	C	D	E
1	Stage 0		TaskType_1	TaskType_2	TaskType_3
2	Stage 1		Run	Wake	Brush
3	Stage 2		Walk	Eat	Floss
4	Stage 3		Stand	Work	WaterPik
5	Stage 4.1
6	Stage 4.2
7	Stage 4.3
8	Stage 5
9	Stage 6/7
10	Admin
11	CPD
12
Lists

Here is my test worksheet
I hope this helps a bit more!

DynamicDataValidationLists.xlsm
	A	B	C
1	Stage	Task Type	Task
2	Stage 3	TaskType_3	WaterPik
3	Stage 0	TaskType_1	Stand
4	Stage 6/7	TaskType_2	Work
5
Sheet3

Cells with Data Validation
Cell	Allow	Criteria
A2:A4	List	=StagesList
B2:B4	List	=TaskTypes
C2:C4	List	=INDIRECT(INDIRECT("$B" & ROW()))

You need to create Data Validation Lists for the Stages, Task Types, and Tasks using the Data Validation menu

The =INDIRECT() formula will be the Data Validation List formula (not on a formula in a cell on your sheet)
If you plan to have multiple rows with different TaskTypes you will need a double INDIRECT function like
=INDIRECT(INDIRECT("$B" & ROW())) - we get the row and column address, use the inside INDIRECT to get the TaskType
the outer INDIRECT selects the Task list for the selected Task Type.

View attachment 96665

Here are the named lists that I created to test this functionality

 

[G Wiz]

Thanks, I will try work through this.

 

[G Wiz]

Oh yes, this works!
Incredible, thank you!