Cell data from one sheet to another based on drop down box

[sbaird]

I am drawing a complete blank… as well as being a noob…

I have a single WB with two sheets.

Sheet 1 has Item (a1), Serial (b1), qty (c1), etc., used for specifically selected items. Think of it as my shopping list.
Sheet 2 has item (a1) and serial (b1) of all possible items. Think of it as a catalog that list available items and stock #s.

I would like sheet 1 Item (a1) to be a drop down box that lists all possible items (from sheet 2 a1))…
And then populate sheet 1 serial (b2) with the sheet 2 serial (b2) that matches the selected item.
And do the same for 15 rows on sheet 1.

Does that make sense?

I know there is an easy answer… and I thought it was VLOOKUP, but I can’t seem to make that work.

 

[Alex Blakenburg]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I would recommend using Excel Tables since it will simplify the ongoing maintenance as you expand the catalog.

1. Convert your catalog in Sheet 2 to a table (select a cell inside the table and press Ctrl+T - Ctrl+L also works and it the latter also works in Web Excel)
2. Give the table a name - Table Design > TableName (far left of toolbar). To follow along use the name "tblCatalog"
3. In Sheet 1 I would also use a table but its optional
4. In Sheet 2 highlight the column A for the rows you want the validation on and go Data > Data Validation > List and assuming you have used my table name copy the following into the Source box =INDIRECT("tblCatalog[Item]")
5. In Sheet 2 column B put this formula into the B2 and copy it down,
   

   =IFERROR(VLOOKUP(A2,tblCatalog,2,FALSE),"")

Let me know how you go.

 

[sbaird]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I would recommend using Excel Tables since it will simplify the ongoing maintenance as you expand the catalog.

1. Convert your catalog in Sheet 2 to a table (select a cell inside the table and press Ctrl+T - Ctrl+L also works and it the latter also works in Web Excel)
2. Give the table a name - Table Design > TableName (far left of toolbar). To follow along use the name "tblCatalog"
3. In Sheet 1 I would also use a table but its optional
4. In Sheet 2 highlight the column A for the rows you want the validation on and go Data > Data Validation > List and assuming you have used my table name copy the following into the Source box =INDIRECT("tblCatalog[Item]")
5. In Sheet 2 column B put this formula into the B2 and copy it down,
   

   =IFERROR(VLOOKUP(A2,tblCatalog,2,FALSE),"")

Let me know how you go.

Doesn't work... In items 4 and 5 in your response, is there something I need to put in Sheet 1?

 

[Alex Blakenburg]

Oops, you are correct. Items 4 & 5 should be for Sheet 1. Sorry for the confusion..