Populating a Range Dependant on a List

[Mckye]

I am having a bit of difficulty with a project I'm working on.
After selecting a category from list1 and an item from list2 on
sheet1, I want a given range on the same sheet to auto populate using
information on sheet 2
As of right now, I have the two dependent lists completed, now I am
working on making the range populate dependent on what is selected in
list2.
I have found a few ways to do this, but none of them are practical
considering the lists reference a few hundred items in 6 categories.

Your assistance on this would be greatly appreciated.

(assuming list1 was in A1 and list2 was in A2, the populated range
would be B2:E2)

 

[Mckye]

On a side note, the information on sheet2 is laid out exactly as I am
trying to insert it, if there were a way of copying the items directly
from sheet2 and pasting them to the right of the selected list item,
that would be ideal, especially if it would automatically copy the
cells laid out to the right of the cell the list is referencing.

 

[Pete_UK]

You don't describe what you have in Sheet2, or how list1 and list2
relate to it. Could it be that List1 is column A of Sheet2, and that
List2 is column B? If so, then insert a new column C in Sheet2 and add
this formula to C1:

=A1&B1

Copy down as necessary.

Then in B2 of Sheet1 you can enter this formula:

=VLOOKUP(A1&A2,Sheet2!$C:$G,COLUMN(B1),0)

and then copy this into C2:E2 of Sheet1.

Hope this helps.

Pete

 

[Pete_UK]

Sorry, you would need to make it:

=VLOOKUP($A1&$A2,Sheet2!$C:$G,COLUMN(B1),0)

if you want to copy it into C2:E2.

Pete

 

[Mckye]

I apologize for not being more descriptive.

On sheet2 is an inventory of a few hundred items, their names and
statistics (such as height, width, weight... etc,). These items are
broken down into 6 categories.
On sheet1 I have an area for displaying these item statistics. When a
category is selected it changes the secondary pull down list to show
only the items in that category. That much I have completed. What I
need now is, when an item name has been selected from list2, I need
the statistics to be populated on the area to the right.

 

[Pete_UK]

Tell me what you have in the columns on both sheets.

Pete

 

[Mckye]

Sheet1
A1 = Category list (Unarmed, Melee, Small Guns, Big Guns, Energy
Weapons)
A2 = Dependant list (If Unarmed is selected in the Category list,
unarmed weapons are shown here.)
B2:F2 = Weapon Statistics for selected weapon (range, ammo, weight,
fire modes)

Sheet2
A1:A247 = Weapon names separated into categories
B2:F2 = Weapon Statistics for A2
B3:F3 = Weapon Statistics for A3
B4:F4 = Weapon Statistics for A4

 

[Pete_UK]

Put this formula in B2 of Sheet1:

=VLOOKUP($A2,Sheet2!$A$1:$F$247,COLUMN(B1),0)

and copy into C2:F2.

Hope this helps.

Pete

 

[Mckye]

Thank you so very much, excellent!

 

[Pete_UK]

You're welcome - thanks for feeding back. It helps when you describe
your data set up accurately <bg>

Pete