Excel 2007 - drop down box range help

[dtex]

Hello All -
I'm new here and looking for help. I have basic knowledge of excel and functions. But what I'm trying to do I am frustrated with.
What I'm looking to do:


I have a two sheets. Sheet 1 is the user sheet. Sheet 2 is the Inputs sheet. (where all allowable data is)
I have made a drop down list in a cell.
I would like another cell to populate data in a drop down list from the inputs sheet depending on what the first drop down list populates. This data it needs to populate is not in a list but in in different cells i.e. K2,K4,k8.

Any help would be great.

Thanks

Dave

[FDibbins]

Hi and welcome to the forum

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[kspeese]

I still consider myself a novice, but I think I got it to work a couple of ways. Maybe someone can provide a better way? If not, this can give you a start. In "Change 2" I basically modified an IF statement in the "Source" of data validation - List (wasn't even sure you could do that before trying this).

[dtex]

Ok I built a sample workbook. I think I know that I will need to use data valuation and vlookup with an indirect function. I guess the big question for this work book is the way the data is set up in the input sheet. I cannot change the way the input sheet is setup.
Thanks for the help!

[FDibbins]

Looking at your file, you currently have no DD's in it, but...

each DD needs to have its own range, and its own range name. so this

	F	G
1	Section 1
2	Module 1	1-I.01
3		1-I.02
4		1-I.03
5	Module 2	No Objectives
6	Module 3	1-I.04
7		1-I.05
8		1-I.06
9	Module 4	1-II.07
10		1-II.08

wont work. You need to have it looking something like this...

	E
1	Module 1
2	1-I.01
3	1-I.02
4	1-I.03
5
6
7	Module 2
8	No Objectives
9
10	Module 3
11	1-I.04
12	1-I.05
13	1-I.06
14
15	Module 4
16	1-II.07
17	1-II.08

with each 1 having its own range name - matching the names un teh "module" range
Also, as far as I know, this is not possible with regular DD...

Also would need to be able to select multiple module numbers

[dtex]

OK I see. My data in the input sheet has to be organized differently and its own range name. I will try rearranging the data in the tab that way and see if I can get it work.

Thanks for looking at it.

[newdoverman]

I have prepared a sample that you can follow to make your own workbook. It uses dynamic, cascading drop-down lists created in tables. I didn't use your data because I think that the real data is much different. You should be able to follow the method used here. Look in the Name Manager to see how the names are created from the tables. Indirect is not used only names are used in the Data validation.

I have tried to insert your data as far as I understand it.

[dtex]

Solved! I have made changes to my inputs tab and ranging the data. This should work. Thanks for the help!