Data Validation from Variable Source List Range

[mobilesuit818]

Hello,
This is my first of my many posts. I am excited to have found a place where we can learn from each other. Anyways, with my question.
I am not keen on the terms that you guys are using, but I will explain the situation.

I have a spreadsheet, in a cell, the data had been validated, and selected to list, so basically, I have a drop down box with 4 options. I would like to find out what it is called AND help figure out a formula so that when one of the items have been selected inside the drop down box, that based on what it selected in the drop down box, there will be certain things or varaiables that will come up in other cells. So lets say for example that in the drop down box, there are the selections of SUV, TRUCK, CAR, MOTORCYCLE. So lets say you scroll and pick SUV, some other fields somewhere on the spreadsheet that some cells have pre-filled from a preselected list based on that selection.

I did a search and found this post by coggo. Im not too sure if they are talking about the same thing.
http://www.mrexcel.com/board2/viewtopic.php?t=10413&highlight=value+specific

I hope that I have provided as much info as I can, since this is my first post, please let me know what I am doing incorrectly.

Much obliged

 

[just_jon]

Check out the link Oaktree's already provided you above.

 

[mobilesuit818]

Check out the link Oaktree's already provided you above.

The issue is that at the end result of that, you have 2 drop down boxes, one being controlled by the other.

The idea kind of works if you have one variable, with single situations.

not only do I have one variable, I need to ask what, how, when , where, about those variables all the same time.

 

[Oaktree]

OK, theoretically suppose that you select plastic from the dropdown box and come up with a way to get the proper attributes to show up.

Then what? Are you going to be entering values for each of those attributes? What happens if you do? If you want to store those values, you really should listen to Joe Was's suggestion and build a relational database in Access.

If all you want to do is type in values and have a calculation tell you a price (or something) based on the inputs, we can help you with that... but, it really sounds like you're trying to build a relational database in Excel, which isn't the right tool for that. Not that it's impossible, but it's kind of like using Excel to write your resume.

 

[just_jon]

Create named ranges like this -

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Products	PlasticW	PlasticH	PlasticD	PlasticT	PlasticB	PlasticTG	PlasticC	MetalW	MetalH	MetalD	MetalT	MetalB	MetalTG	MetalC	MetalTX
2	Plasrtic	Choice1	Choice1	Choice1	Choice1	Choice1	Choice1	Choice1	Choice1	Choice1	Choice1	Choice1	Choice1	Choice1	Choice1	Choice1
3	Metal	Choice2	Choice2	Choice2	Choice2	Choice2	Choice2	Choice2	Choice2	Choice2	Choice2	Choice2	Choice2	Choice2	Choice2	Choice2
4	Wire	Choice3	Choice3	Choice3	Choice3	Choice3	Choice3	Choice3	Choice3	Choice3	Choice3	Choice3	Choice3	Choice3	Choice3	Choice3
5	Apparel
Sheet1

where row #1 is the name of the range below.

Then, aassuming you use the List Products as the data validation source in A1 you can then use elsewhere in the sheet

=INDIRECT(A1&"W") as a source for Width options,
=INDIRECT(A1&"D") as a source for Depth options,
etc

 

[mobilesuit818]

OK, theoretically suppose that you select plastic from the dropdown box and come up with a way to get the proper attributes to show up.

Then what? Are you going to be entering values for each of those attributes? What happens if you do? If you want to store those values, you really should listen to Joe Was's suggestion and build a relational database in Access.

If all you want to do is type in values and have a calculation tell you a price (or something) based on the inputs, we can help you with that... but, it really sounds like you're trying to build a relational database in Excel, which isn't the right tool for that. Not that it's impossible, but it's kind of like using Excel to write your resume.

Once we get the attributes to show up, I am going to enter data in to columns next to the attributes, not in it. I am not too sure if you were asking this, but I just wanted to make this clear. The attributes are there to define the variable. There are going to be text in there, not numbers, so there are no need for calculations, or drop down boxes.

I dont know whether I can do this with Excel, or Access, and you guys have been great, I havent learned this much in one week, since school.
I am trying everything you guys are telling me to do.

I am new to this, cause I thought that Excel was limitless.

You guys rock!!

 

[just_jon]

If you are trying to get row headers to appear down column A that are dependant on the entry in A3, then use an change event macro, looking at A3 changes, to seed the correct headers.

 

[PolarBear]

I think I did something similar recently. I have a bunch of sales teams, identified by their managers' name. They wanted to print out specific data, such as cancelled sales, for each rep on their team, quickly. So, I created a form where a manager would choose his name, and the type of data he was interested in. The sheet would then populate with the name of each rep in column A, and the requested data in columns B,C,D, etc.
for as many rows as that rep had data (e.g. 2 rows if he had two cancelled sales, 9 rows if he had 9 cancels, etc.). Then the sheet was automatically printed, and the process repeated for the next rep. (Yes, if they knew how to use pivot tables, this would be trivial, but they don't.)

Anyway, I know you don't need the autoprint functions, but what about a form that popped up, let you choose one of your four options, and then populated column A with the sub-options? Is that what you're trying to accomplish?

 

[mobilesuit818]

I think I did something similar recently. I have a bunch of sales teams, identified by their managers' name. They wanted to print out specific data, such as cancelled sales, for each rep on their team, quickly. So, I created a form where a manager would choose his name, and the type of data he was interested in. The sheet would then populate with the name of each rep in column A, and the requested data in columns B,C,D, etc.
for as many rows as that rep had data (e.g. 2 rows if he had two cancelled sales, 9 rows if he had 9 cancels, etc.). Then the sheet was automatically printed, and the process repeated for the next rep. (Yes, if they knew how to use pivot tables, this would be trivial, but they don't.)

Anyway, I know you don't need the autoprint functions, but what about a form that popped up, let you choose one of your four options, and then populated column A with the sub-options? Is that what you're trying to accomplish?

I think that is what I am looking for if I am reading it correctly.
do you wanna post a sample or formula or

 

[mobilesuit818]

If you are trying to get row headers to appear down column A that are dependant on the entry in A3, then use an change event macro, looking at A3 changes, to seed the correct headers.

Let me try this. Ill let you know.

 

[PolarBear]

OK, here's what I have: the sheet "Lookup for teams" has each rep's name, their repID number, and their team manager's name. Col D either has the name of their program, or it says "Inactive" (we have a lot of turnover). Your case would be simpler, I think. You would have a sheet (possibly hidden so users can't futz with it?) where column A might be
Metal (for five or six rows), Plastic for six or seven rows, etc., while
column B would be the attributes you've listed.

I use my code to set up a listbox on the form, but you could just change the destinations sheet to copy your stuff directly to the desired sheet. I have it setup to populate Listbox1 on the form; the managers then can choose to print out all the reps or just a subset. You don't appear to need that extra functionality. Anyway, here's the code:

Public Sub MakeTeams(TeamName As String)
' Makes a list for the listbox used on the RepTE user form

Dim LookupRows As Integer 'Total number of rows on LOOKUP FOR TEAM sheet
Dim RC As Single 'A row counter
Dim Team As String

' First, clear out old data from Rep Detail sheet
Sheets("Rep Detail").Activate
Sheets("Rep Detail").Range("K2:M60").ClearContents

'Go to Lookup sheet and find how many rows there are
Sheets("LOOKUP FOR TEAM").Activate
LookupRows = Sheets("LOOKUP FOR TEAM").Cells(Rows.Count, "A").End(xlUp).Row

Team = TeamName ' just use kim while testing; in active use, will be the team manager's name
Sheets("Rep Detail").Range("B5").Value = Team 'Store team name here

' Set up a counter to move through the rows
RC = 2 ' Always start in second row of "Lookup for Team"
NumReps = 0 'Initialize number of reps
' Now set up loop to find the team manager's name, and then copy the
' team name, rep name, and rep number to the rep detail sheet

Do Until RC > LookupRows
    If Team <> Sheets("LOOKUP FOR TEAM").Range("C" & RC).Value Then
        RC = RC + 1 ' if no match to team name, go to next row
    Else
        If PrintInactive = False Then
            If Sheets(1).Range("D" & RC) = "Inactive" Then
                RC = RC + 1 'skip this line if rep is inactive
            Else 'Copy the name data for active reps to the Rep Detail sheet
                Sheets("Rep Detail").Range("K" & NumReps + 2).Value = Sheets(1).Range("B" & RC).Value
                Sheets("Rep Detail").Range("L" & NumReps + 2).Value = Sheets(1).Range("D" & RC).Value
                Sheets("Rep Detail").Range("M" & NumReps + 2).Value = Sheets(1).Range("A" & RC).Value
                NumReps = NumReps + 1
                RC = RC + 1
            End If
        Else ' Copy the name data for all reps, regardless of inactive or not
            Sheets("Rep Detail").Range("K" & NumReps + 2).Value = Sheets(1).Range("B" & RC).Value
            Sheets("Rep Detail").Range("L" & NumReps + 2).Value = Sheets(1).Range("D" & RC).Value
            Sheets("Rep Detail").Range("M" & NumReps + 2).Value = Sheets(1).Range("a" & RC).Value
            NumReps = NumReps + 1
            RC = RC + 1
        End If
    End If
Loop



End Sub

This way, if you have 8 attributes for one of your four choices, and only six for another, it's no problem; it only copies the entries which are in the same row as your choice (team name in my case, plastic/metal etc. in yours). I have some teams with 22 members and some with only ten. Note that the space I clear is on the RepDetail sheet to receive the information is 59 rows; I wanted to make sure that I was always clearing out any old data.

Let me know if this is useful...