Excel/Excel 2012

Advertisement


Question
Main Form
Main Form  
I am trying to create a form in Excel 2010 that has a section that auto populates based from certain drop down selections.  Please see the attachment of the form.



Under Section C - The cell with the heading "Reason for Change" will be a drop down.  With any selection in this drop down, I want it to auto-populate Section E with 4 mandatory programs and their administrators.

Section D will have three drop downs for: Department, Area and Position.  Depending on the combination of the three, I want the appropriate additional programs and administrators to fall after the 4 mandatory programs that should already be there from Section C.

I have already created the lists on separate worksheets for all the drop downs, as well as the programs and administrators.


Also, I notice with my drop downs that you have to scroll through and make the selection, or type in the exact spelling.  Is there a way to start typing a selection, and have the drop down begin to fill in the rest. (I.e. - if I type "a", it will show all the drop downs that start with a)




This is only part of my requirements, and conditions, but if you could point me in the right direction, I can figure the rest out myself.

Answer
Aaron:

This auto-fill can be done in Excel; however, it requires some slight trickery.

To accomplish this task, the range of cells being used for your data validation list box needs to be located immediately above (or below) the cell with the drop-down box.

You can then hide this row so it doesn't appear to the users; however, when manually typing in the box, it will auto-fill based on what appears in the list above (or below#.

I have attached a link to a sample spreadsheet here...

https://www.dropbox.com/s/8iy48uzgwn88lgk/AaronAllexpert.xlsx?m

Now the first part of your question didn't have any specific question; however, I believe the answer is going to be VLOOKUP #or, at worst, INDEX and MATCH).  These functions will allow you to take the results of your drop-down box and autopopulate other cells based on these results.  I'll start off by sending you info on VLOOKUP:

http://www.timeatlas.com/5_minute_tips/general/learning_vlookup_in_excel

Please remember to donate if I answered your question!
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Nathan

Expertise


This expert requires a donation of $5 for questions answered (satisfaction guaranteed). Isn't $5 a value for this experts time?
Microsoft Excel questions related to advanced formulas, Pivot Tables, filters, forms, graphs, and just about anything else (EXCEPT Visual Basic Coding/Programming and Macros, I don't answer questions in those categories).

Experience

I have been using spreadsheets since Lotus 1-2-3 was released.

Education/Credentials
Certified Public Accountant (CPA)

©2016 About.com. All rights reserved.