Drop down menu-import layout

Hello-

I have an excel template we use to import product data into one of our systems. I need to find the correct "formula" that would allow us to select from a drop in one sheet, the correct category and then automatically update the cell next to it with the categories correct "ID". All of our categories and category ID's are in the same workbook but in a different sheet. We can use the drop down now, which shows all the categories in the sheet, but it will not include the category ID. I tried to attach our import book but no luck.


The "import layout" sheet includes the "category ID" and "category" headers. The category column has a drop down which is derived from sheet "categories". In sheet "categories" you see the actual category listing and in the adjoining cell, it's corresponding "ID".

When the correct category is selected on the importlayout sheet, the corresponding (and correct) category ID needs to populate the "category ID" cell.

How do I do this?

Hello Mikeyy,

Welcome to the Forum!

Sometimes it is difficult to upload files workbook files to the forum due to various reasons. When this happens, you can try to upload the workbook again, or you can zip it and then upload it. I have had more consistent results posting zipped files. Give it try.

Sincerely,
Leith Rross

Lets try a zip then-

Hello Mikeyy,

Have a look at the link below. After looking at your workbook, I think this approach will work for you.

Excel -- Data Validation -- Create Dependent Lists

Sincerely,
Leith Ross

Thanks. I read through it a couple of times and see where it may work. I got sidetracked on the dependant area as my data is in a different sheet. The example seems to refer to the source data being in the same sheet.

Okay...I took a look at this and don't see where it will accomplish what I need. I have data in two separate sheets. This example does not address that. I did do some other exploring and I don't think this formula will work. It appears this is designed to pull a drop down list based on an entry....I need it to pull one cell based on an entry.

Originally Posted by ratcat

G'day and welcome to the forum,

Try the vlookup formula.

Vlookup by itself will not do it. I am not fluent with writing formulas, where can one find a quick tutorial so I can get this knocked out?

It looks like that is the right formula...just need to move the cells around to fit the columns in our sheet. I will give this a try tonight. Thanks a bunch!

RAT-

I need a little more assistance...it appears your example has the data in different places and I went back and edited your formula a few times to fit our sheet but couldn't get it right. I uploaded our document with the columns in the correct place...can you advise what the difference might be in the formula to fit our layout?

Thanks RAT.

I changed the order of the Category and Category ID fields on the categories sheet in my workbook per your previous post. I copied your formula and pasted it into the correct field in the importlayout sheet in my workbook. The Category ID and Category fields in my importlayout sheet are in the "U" and "V" columns. I edited your formula to reference this. However, I keep getting an NA returned in the formula. What am I doing wrong?

The workbook in question is attached.

Originally Posted by ratcat

G'day Mikeyy,

Simple, you have to change the table array in the formula. The Category ID value that you are trying to return against the Category name was at row A467. The current formula was only looking down as far as row 9. Since the data goes to row 3340, enter the formula in U3, then copy drag down.

Please Login or Register  to view this content.

Please post again if more help is needed or just PM.

Cheers

Hey RAT---we figured it out last night. Acutally the wife did. Just want to say thanks for your help. I really apprecaite it.