Filling Cells Based on Drop Down Selection

**Dredger** · 01-11-2011, 11:50 AM

Hi all,

I am trying to make a Maintenance Code spreadsheet for work. What I want to do is have a drop down list on Sheet 1 with 5 types of codes listed. Then upon selecting one of the code types have the Codes and descritpions load in the cells below the drop down menu.

I am not very knowledgeable in Excel, so if you have the answer please dumb it down for me. I tried VLOOKUP but never got what I was after. I am hoping that the format will appear the same as it currently is. Also the other types of codes are more numerous.

Attached is what I was working on. At this point I only have the Codes and
Descriptions for one of the 5 code types.

EDIT: THANKS NBVC

**NBVC** · 01-11-2011, 12:40 PM

You need to name the tables something that can be used in a lookup to reference it.

So try naming the tables the same as your dropdown choices, but remove the spaces...

e.g. TypeMaintenanceCodes

Then in A2:

=IFERROR(INDEX(INDIRECT(SUBSTITUTE($A$1," ","")),ROWS($A$1:$A2),1),"")

AND IN B2:

=IFERROR(INDEX(INDIRECT(SUBSTITUTE($A$1," ","")),ROWS($A$1:$A2),2),"")

copied down as far as you want and adjust column B to wrap text.

**Dredger** · 01-11-2011, 01:35 PM

I tried your commands. They are exactly what I am looking for! Only problem I see is that the Type Maint Codes are starting from Code B instead of A. Do you know what I need to do to fix this?

Other than that small issue this was perfect. Thanks so much for taking the time and having the knowledge to help!

**NBVC** · 01-11-2011, 01:39 PM

Check your Formulas|Name Manager for that Range and check that it does in fact cover the right entire range.

**Dredger** · 01-11-2011, 01:44 PM

I'm not sure. I checked the TypeMaint Range and it covers $A$1:$B$10. I tested out the ActionTakenCodes Range that I made and it also does not show the first line of codes. Is this maybe because I have the dropdown menu in A1?

**NBVC** · 01-11-2011, 01:49 PM

Oh, you mean first row... I thought you were talking columns... sorry.

Try formulas:

=IFERROR(INDEX(INDIRECT(SUBSTITUTE($A$1," ","")),ROWS($A$1:$A1),1),"")

and

=IFERROR(INDEX(INDIRECT(SUBSTITUTE($A$1," ","")),ROWS($A$1:$A1),2),"")

**Dredger** · 01-11-2011, 02:02 PM

Alright, two more questions then I will stop bugging you.

First, starting at B12:B35 I am only getting the first line of text. I have highlighted and turned Wrap Text on and off a few times to no avail. Any ideas how to get the rest of the data in there and visible?

Secondly, do you know a way to edit wrap text so it is so spacious? I made my B Column wider so you could see more codes but with wrap text on it still is very narrow down the middle instead of filling out the new lager cell. I tried adjusting the size of the cells on Sheet 3 but that didnt change the referenced data on Sheet 1.

**NBVC** · 01-11-2011, 02:11 PM

Not sure what you mean by:

starting at B12:B35 I am only getting the first line of text

Is this in the sample you attached? If so, it would end at A11:B11 and therefore nothing would be in A12:B12 down.

You can replace the carriage return character in the individual ranges by going to Home|Find and Replace and choose Replace..

Find What: (Hold down the ALT key and enter 010 with the number pad of your keyboard).

Replace With: (leave this blank).

you may need to tidy up the results a bit by introducing a new hard return after each title in each cell... and removing the Bold.

**Dredger** · 01-11-2011, 02:22 PM

I am attaching the progress on the spreadsheet. In Actions Taken Codes on the drop down menu if you scroll down to A12 and B12 it shows the first line of text instead of the whole description.

I extended the command you posted for A and B columns to row 210. I will be making more if these code lists for the remaining 3 blank drop down options.

Maybe seeing it will make it clear since I am not explaining it well.

**NBVC** · 01-11-2011, 02:26 PM

Expand those rows in B12 downwards and you will see the full content.

**Dredger** · 01-11-2011, 02:31 PM

Hehe, thats embarrassing.
Well NBVC, you are the best. Thank you for all of your quick responses. You have made my life so much easier. You had answers and fixes for all my questions. Thank you!

**Dredger** · 01-11-2011, 06:14 PM

Hi again,

I have continued working on the Maint Code spreadsheet. However it isn't coming together like it is in my head, maybe it can't I am not sure.

My latest, umm issue, is that the while I can link the drop down menu selection to a particular cell range that data in there is all not keeping format when showing up on Sheet 1. Is there a way to make the cells auto adjust to the size/shape they are in Sheet 3 where I am making the cell ranges?

Also, there are alot of HowMal codes and they are broken down into three categories. At first I wanted to select How Mal codes from the drop down then have a second Drop down appear to select between Electricla, Physical, and No Defect. I couldnt figure it out so I made headers in the range cells and grouped them together according to the type of Mal. This worked great on sheet 3 but doesn't translate over to Sheet 1 when set up.

I am attaching the spreadsheet so you can see what I mean.

This isn't just for NBVC, poor guy has been stuck dealing with me all day. Anyone and everyone can look and offer advice. I want to make this easy to use, intuitive, and well made.... And never have to do it again.

**NBVC** · 01-12-2011, 09:27 AM

In order for the row heights to adjust automatically when using data validation, you need to have a Worksheet_Change event macro running in the background.

If you right-click the sheet tab and select View Code you will see the following simple macro:

Please Login or Register  to view this content.

This should adjust row heights to match what you have in Sheet3.

Note: You will need to enable macros in this workbook.

**Dredger** · 01-12-2011, 06:07 PM

Spectacular! You sir are an Excel wizard. Thank you so much!

**tinkertot** · 06-07-2011, 01:37 AM

Would just like to say thank you very much for this, it was the exact same problem I was having and you've helped me no end. Now if I could work out how to have multiple drop downs on the one sheet that auto fill other cells I'd be a happy girl. But this is enough for one day

Filling Cells Based on Drop Down Selection

LinkBack

Thread Tools

Rate This Thread

Display

Filling Cells Based on Drop Down Selection

Re: Filling Cells Based on Drop Down Selection

Re: Filling Cells Based on Drop Down Selection

Re: Filling Cells Based on Drop Down Selection

Re: Filling Cells Based on Drop Down Selection

Re: Filling Cells Based on Drop Down Selection

Re: Filling Cells Based on Drop Down Selection

Re: Filling Cells Based on Drop Down Selection

Re: Filling Cells Based on Drop Down Selection

Re: Filling Cells Based on Drop Down Selection

Re: Filling Cells Based on Drop Down Selection

Re: Filling Cells Based on Drop Down Selection

Re: Filling Cells Based on Drop Down Selection

Re: Filling Cells Based on Drop Down Selection

Re: Filling Cells Based on Drop Down Selection

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions