Copy cells based on selection from drop down

[ericbrunson]

This may be a fairly basic question, but I haven't found it in the archives.

I've defined a list of parts in 3 columns: $x,$y and $z.
I've made column $a into a drop down of values from $x.

When someone selects a value from the column $a drop down I want to copy the data from the corresponding row in $y and $z into columns $b and $c.

Ideally, I would like to make the cells in $b and $c a reference to the appropriate cells in $x and $y, but I'm not sure if that's asking too much.

Finally, any pointers to good, free VBA tutorials on the net?

Thanks,
e.

 

[SkipVought]

Hi,

What kind of drowdown, Forms or Control Toolbox?

You may not need any VBA to do this. It's a simple lookup.
[tt]
B1: =Index(y:y,IndexOfSelection,1)
[/tt]
or
[tt]
B1: =Vlookup(Selection,X:Z,2)
[/tt]
depending on the type of dropdoen box.

Forms gives you an Index of Selection.



Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[ericbrunson]

Skip,

Thanks a bunch, I got *way* farther with your method, but I don't understand some of what you said.

I'm not sure whether I'm using a form or a control, I created the dropdown by selecting 'Validate' from the Data Menu and defining the range over $x. When I made the values in $x integers corresponding to the row numbers then B1: =Index(Y:Y,a1) worked.

But I don't want to use numbers, they're going to be part codes, so I think a vlookup is the route to take. I'm currently looking through the online help trying to define a table for the vlookup, when I figure that out, I'll try changing the index to a vlookup.

Let me know if I'm heading down the wrong path, but hella thanks for the help so far.

 

[SkipVought]

OK, you're using a data validation dropdown. Fine!

Is the value in column A is not an index, then use the VLOOKUP method.

Caveat: Your table X:Z must be sorted by column X, ascending.
[tt]
B1: =Vlookup(A1,X:Z,2)
C1: =Vlookup(A1,X:Z,3)
[/tt]


Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[ericbrunson]

Excellent!

That's what I figured out and was just coming back to report success. I ended up putting FALSE for the range_lookup so the table doesn't have to be sorted, but other than that it's the exact answer you gave.

Thanks again!