Drop Down List - Other Cell Results

[Sondra]

Multiple Part question:

Created an Invoice with various cells. There is a Customer Name, Address,
City, State, Zip and Phone Number. I've created a separate worksheet that
contains the Customer information. I created a Drop Down Box on the Invoice.
Created a VLOOKUP for the Address, City, State, Zip and phone number Cells.

=VLOOKUP(D12,CUSTOMER!A2:F6,2,TRUE)

Question 1:

The 1st few records work perfectly.
1. I choose the Customer from the drop down list and the remaining fields
self-complete.
2. There are several records that won't populate. They show on the drop
down but none of the fields populate when you choose the customer.
3. I have verified there is data in the VLOOKUP fields

Any suggestions would be great.

Question 2:
How do I make the Customer Field be empty until I choose the drop down arrow.

Thanks in advance.

 

[Conan Kelly]

Sondra,

Make sure you reference to the table on the "CUSTOMER" sheet is Absolute,
not relative like it is now.

Change your formula from...

=VLOOKUP(D12,CUSTOMER!A2:F6,2,TRUE)

....to...

=VLOOKUP(D12,CUSTOMER!$A$2:$F$6,2,TRUE)

Check out this web page for more info on Absolute and Reletive references:
http://www.cpearson.com/excel/relative.aspx

Is this formula the one used in the Customer Field? If so, us and IF()
function to make it appear blank until a customer is selected.

The IF() function looks something like this:

=IF(logical test that results in TRUE or FALSE, value if true, value if
false)

so your formula would look something like this:

=IF(D12="","",VLOOKUP(D12,CUSTOMER!$A$2:$F$6,2,TRUE))

HTH,

Conan Kelly

 

[Sondra]

I changed my formula as you suggested:

=VLOOKUP(D12,CUSTOMER!$A$2:$F$6,2,TRUE)

But only the 1st 3 records in the "CUSTOMER" worksheet actually populate
when I choose a name from the dropdown list.

Please advise why only the 1st three would work and not any other.

The second part of my previous question didn't work with the formula you
suggested:

=IF(D12="","",VLOOKUP(D12,CUSTOMER!$A$2:$F$6,2,TRUE))

I basically need the Customer cell blank when the form is opened. Currently
the last customer remains in the cell until I change it.

Thanks for your help.

 

[Conan Kelly]

Sondra,

This Drop Down Box you created on the Invoice sheet...is it an in-cell
drop-down created via "Data" menu > "Validation..." submenu > "Allow:" =
"List" > etc...or is it an actual Combo Box control from the Forms Toolbar
or the Controls Toolbox (be sure to tell me which one...Forms Toolbar or
Controls Toolbox...they are 2 different controls that behave differently)?

When you say...

But only the 1st 3 records in the "CUSTOMER" worksheet actually populate
when I choose a name from the dropdown list.

....does that mean...when you click the drop down, only 3 names show up in
the drop down...or...when you choose name 4 or 5 from the drop down (by your
formula, looks like you only have 5 customers), your data doesn't fill in,
but fills in fine for names 1-3?

Without being able to look at your file, I'll need more info from
you...mainly the questions asked above...but also, the addresses of the
customer data cells on the invoice sheet, the formulas in each of those
cells, etc...

As for leaving the Customer cell blank when the form is opened...

....when you say "when the form is opened"...do you mean the Invoice
sheet...or...is there an actual UserForm involved here? Do you want the
Customer cell on the Invoice sheet blank when you open the file/workbook?
If so, do you make changes to the file that need to be kept...ie., make
design changes to the Invoice or add new customers? If not, save it once
with the cell blank, then just close the file w/o saving each time you use
it. If you need to save changes, then it can be done...with macros/code.
How much do you know about macros/writing code? Kinda related to this...is
this a Template file that other files are created from?

HTH,

Conan

 

[Sondra]

Conan:

Thank you for getting back to me. Here are the answers:

This Drop Down Box you created on the Invoice sheet...is it an in-cell
drop-down created via "Data" menu > "Validation..." submenu > "Allow:" =
"List" > etc...or is it an actual Combo Box control from the Forms Toolbar
or the Controls Toolbox (be sure to tell me which one...Forms Toolbar or
Controls Toolbox...they are 2 different controls that behave differently)?

****Data > Validation > Submenu > etc…

When you say...


....does that mean...when you click the drop down, only 3 names show up in
the drop down...or...when you choose name 4 or 5 from the drop down (by your
formula, looks like you only have 5 customers), your data doesn't fill in,
but fills in fine for names 1-3?

****Chose name 4 or 5 from the drop down the data doesn’t fill in as it does
for 1-3

Without being able to look at your file, I'll need more info from
you...mainly the questions asked above...but also, the addresses of the
customer data cells on the invoice sheet, the formulas in each of those
cells, etc...

****Date is made up so no problems sharing:

Mary Emery 123 Lake Avenue Boise ID 83704 (208) 123-4567
Michelle Clifford 987 Circus Circus Circle Las Vegas NV 12345 (702) 987-6543
Rosa Hajjar 742 Marine Corp Lane Camp Pendleton CA 92058 (760) 456-9871
Barbara Sterling 1098742 Mt. Hood Avenue Seattle WA 65498 (360) 852-9631
Mariah Malcolm 555 Oregon Coast Way Seaside OR 98765 (503) 159-7535

Address =VLOOKUP(D12,CUSTOMER!$A$2:$F$6,2,TRUE)
City=VLOOKUP(D12,CUSTOMER!$A$2:$F$6,3,TRUE)
State=VLOOKUP(D12,CUSTOMER!$A$2:$F$6,4,TRUE)
Zip=VLOOKUP(D12,CUSTOMER!$A$2:$F$6,5,TRUE)
Phone=VLOOKUP(D12,CUSTOMER!$A$2:$F$6,6,TRUE)

As for leaving the Customer cell blank when the form is opened...

....when you say "when the form is opened"...do you mean the Invoice
sheet...or...is there an actual UserForm involved here? Do you want the
Customer cell on the Invoice sheet blank when you open the file/workbook?
If so, do you make changes to the file that need to be kept...ie., make
design changes to the Invoice or add new customers? If not, save it once
with the cell blank, then just close the file w/o saving each time you use
it. If you need to save changes, then it can be done...with macros/code.
How much do you know about macros/writing code? Kinda related to this...is
this a Template file that other files are created from?

****Invoice Sheet
I believe I have figured this one out with a Macro & writing code.

THANKS SO MUCH!!!

 

[Conan Kelly]

Sondra,

How wierd!!! I got the same results as you...then I changed all the TRUE's
in your formulas to FALSE (more specifically "0"...but should be the same
thing), and everything worked. I have no idea what is going on here.

I'm assuming with your data validation drop down list you are using a named
range to fill in the customers names. What about adding new customers? Are
you accounting for that?

HTH,

Conan