Auto Fill cells from a list onto another sheet

[cproskie]

I actually have a couple questions here now.

First, I have a user data base on one sheet with contact information that is all on one row and I am trying to copy select data from the data base onto an order form. I have a drop list box to select a name on the order form and I want the contact info to copy onto the form when the person is selected.

Second, on the same order form I'm having issue trying to populate a drop list depended on the selection from another drop list. For example, if someone selects T-shirt from the first drop list I want to automatically create another drop list with colour options that are specific to that T-shirt. I have a list of options already created on a master item sheet and just need to know how to create the second drop list with the options on the master item sheet.

I really hope someone can help.

 

[zrx1200]

I actually have a couple questions here now.

First, I have a user data base on one sheet with contact information that is all on one row and I am trying to copy select data from the data base onto an order form. I have a drop list box to select a name on the order form and I want the contact info to copy onto the form when the person is selected.

Second, on the same order form I'm having issue trying to populate a drop list depended on the selection from another drop list. For example, if someone selects T-shirt from the first drop list I want to automatically create another drop list with colour options that are specific to that T-shirt. I have a list of options already created on a master item sheet and just need to know how to create the second drop list with the options on the master item sheet.

I really hope someone can help.

I'm going to assume your using excel spreadsheets, only reason I say this is you mention data base a few times.

Never the less have you used VBA for excel before as this is what you'll need to get some of this done.

 

[jbeaucaire]

VBA shouldn't be required, but this is semi-advanced process. You can do it, though.


1) You will use a VLOOKUP() in your customer info section to display the data that matches the selected customer.

2a) You will create named ranges on another sheet. In each named range will be the color options for that particular named range. You will most likely have many combinations. Let's call the color options Colors1, Colors2, Colors3, etc...

2b) Next you will create a range called TShirt and it will be a list of the TShirts available to choose from. In the next column you will enter the Colors1, Colors2, etc... that is appropriate for each TShirt.

2c) You will also create named range called TShirtCOLORS that is the both Tshirt column and the colors, both columns created in step 2a above.

3) Back on your main form you will use INDIRECT() formulas in your data validation to select a dynamically changing "Drop down" based on a previous cells choice.

If you selected TShirt in A1, the Data Validation in B1 could be:

Allow: List
Source: =INDIRECT(A1)

So when "TShirt" is selected in A1, the drop down in B1 would become the named range we created earlier also called TShirt. Cool!

In C1, to get the color options to appear, the Data Validation would be:

Allow: List
Source: =INDIRECT(VLOOKUP(B1, TShirtCOLORS, 2, 0))

That should get you going.

This stuff is fickle, follow the steps carefully, keep trying. If you get stuck, send me your file so I can see what might be going wrong in your attempts... use the link in my signature below to send me the file. Try it yourself first...

 

[zrx1200]

VBA shouldn't be required, but this is semi-advanced process. You can do it, though.


1) You will use a VLOOKUP() in your customer info section to display the data that matches the selected customer.

2a) You will create named ranges on another sheet. In each named range will be the color options for that particular named range. You will most likely have many combinations. Let's call the color options Colors1, Colors2, Colors3, etc...

2b) Next you will create a range called TShirt and it will be a list of the TShirts available to choose from. In the next column you will enter the Colors1, Colors2, etc... that is appropriate for each TShirt.

2c) You will also create named range called TShirtCOLORS that is the both Tshirt column and the colors, both columns created in step 2a above.

3) Back on your main form you will use INDIRECT() formulas in your data validation to select a dynamically changing "Drop down" based on a previous cells choice.

If you selected TShirt in A1, the Data Validation in B1 could be:

Allow: List
Source: =INDIRECT(A1)

So when "TShirt" is selected in A1, the drop down in B1 would become the named range we created earlier also called TShirt. Cool!

In C1, to get the color options to appear, the Data Validation would be:

Allow: List
Source: =INDIRECT(VLOOKUP(B1, TShirtCOLORS, 2, 0))

That should get you going.

This stuff is fickle, follow the steps carefully, keep trying. If you get stuck, send me your file so I can see what might be going wrong in your attempts... use the link in my signature below to send me the file. Try it yourself first...

Me like!

 

[swalk72]

Hi, I'm very new to excel and am trying to do something similar but haven't been successful. Thank you for any help you can provide. Here's what I'm trying to do...

I have a sheet that has 2 columns: DocType (Column G) and SubType Column H). There are SubTypes associated with each DocType on another sheet setup as a Named Range called DocType_Subtype (see format below). When they select the DocType I need the SubTypes list to be populated with only the SubTypes associated with that DocType. I'm using the following validation in the SubType column based on the thread: =INDIRECT(VLOOKUP(G2, DocType_Subtype, 2, 0)).

The SubType list is not being populated when DocType is selected. What am I doing wrong?


Doctype Docsubtype
Audit Schedules Audit Schedules

Business Continuity Plan Business Continuity Plan

Computer Automated System Computer Automated System
LIMS Document
Manufacturing Execution System Document
Room Monitoring System Document

Protocol APN
Clinical Stability Reports
Protocol
Report
Report-Protocol