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

 

[Peter_SSs]

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.

I did not understand that. In what way does this fail?

G Wiz.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
Sheet3

Cells with Data Validation
Cell	Allow	Criteria
C2:C4	List	=INDIRECT(B2)

 

[G Wiz]

One issue I do have here is the Indirect cell doesn't like being copied and has much more limited flexibility than the other cells.

 

[Peter_SSs]

the Indirect cell

Which cell(s) exactly are you referring to with that remark?

doesn't like being copied and has much more limited flexibility than the other cells.

Can you expand, with example(s), on just what you mean by that too? I could not find a particular problem in copying any of the cells. Perhaps I wasn't doing what you want to do.

 

[Bosquedeguate]

One issue I do have here is the Indirect cell doesn't like being copied and has much more limited flexibility than the other cells.

Your data validation lists are set up with the Data Validation screens form Data (on the main tool bar) -> Data Validation. You should not need to enter an =INDIRECT() formula in cells for the solution you are seeking.

 

[G Wiz]

Revisiting this, the copying issue I don't seem to have now. But there is an issue that I have when selecting a Task. It only gives me the option in the drop down box of the first 40 cells. Because the formula is: =INDIRECT(INDIRECT("$H" & ROW())) and there are no cells references, and there is no way to drag the range, I don't know how I can correct this to include more cells? Please help!

 

[G Wiz]

Sorted. Just had to adjust in the Name Manager...