Data Validation ( no blanks) based on a dependant cell

[nobbyclarke]

HELP

Ok so I want to be able to select in cell; A1 either a tool or a task (fixed data) using a drop down list.

Once I select what I want in cell A1; I want Cell B1 to have a drop down list that refers to a list of either tools or a list of tasks depending what is selected in cell A1.

In the list of tools I have 300 cells about 75 of them have a tool in there (eg. Drill - Makita - GMW1324 - (SN#4321))

in the list of tasks I have 300 cells; about 120 of them have tasks in there (eg. Cleaning carpet or washing walls)

here's the glitch...

because there are 300 line entries for each list (tool and tasks) I keep coming up with a data validation drop down box which is mostly full of white space and you have to scroll all the way to the top to get what tool or task you want (dependant on what you select in A1) .

Someone please help me here

 

[Eric W]

Here's an option:

Excel 2010

	A	B	C	D	E	F	G
Task	Tool	Measure
Task	Hammer	Measure	Cut
Install
Screwdriver	Document
Wrench	Cut	Clean-up
Install
Drill	Document
Sander
Clean-up
Saw

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]Tools[/TD]
[TD="align: center"]Tasks[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

</tbody>

Sheet9​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D3[/TH]
[TD="align: left"]=IF(A1="Tool",0,1)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D4[/TH]
[TD="align: left"]=COUNTIF(OFFSET(E2,0,$D$3,300,1),"<>")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G1[/TH]
[TD="align: left"]{=IFERROR(INDEX(OFFSET($E$1,0,$D$3,300),SMALL(IF(OFFSET($E$1,1,$D$3,300)<>"",ROW($E$2:$E$300),9E+99),ROW()-ROW($G$1)+1)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Obviously you can put these pieces in different places, but this example shows how it can work. D1:D2 is the data validation list for A1. D3 is just a formula returning a 0 if Tool is selected, and 1 if Task is selected. D4 is a formula counting how many items are in the selected column. This example shows Task being selected, so there are 5 tasks in column F.

The formula in G1 looks at the selected column (E or F) and removes empty rows. Put the formula in G1, confirm it with Control-Shift-Enter. Then select cell G1 and paste it down the column.

Finally, click on cell B1, then Data Validation from the Data tab, select List and give it a source of
=OFFSET(Sheet9!$G$1,0,0,Sheet9!$D$4)

I'm testing on Sheet9, adapt as needed.

That should do it!

Let me know how it works.

 

[nobbyclarke]

you superstar it worked thankyou very much for your help.

Stilla newby to proper playing in excel but I am learning slowly.

thank you once again

 

[Eric W]

You're very welcome.