Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 12
Default Drop Down List - Other Cell Results

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.
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 419
Default Drop Down List - Other Cell Results

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,TRU E))

HTH,

Conan Kelly




"Sondra" wrote in message
...
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.



  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 12
Default Drop Down List - Other Cell Results

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,TRU E))

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" wrote:

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,TRU E))

HTH,

Conan Kelly




"Sondra" wrote in message
...
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.




  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 419
Default Drop Down List - Other Cell Results

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" wrote in message
...
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,TRU E))

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" wrote:

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,TRU E))

HTH,

Conan Kelly




"Sondra" wrote in message
...
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.






  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 12
Default Drop Down List - Other Cell Results

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...

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?


****Chose name 4 or 5 from the drop down the data doesnt 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" wrote:

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" wrote in message
...
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,TRU E))

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" wrote:

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,TRU E))

HTH,

Conan Kelly




"Sondra" wrote in message
...
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.








  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 419
Default Drop Down List - Other Cell Results

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





"Sondra" wrote in message
...
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...

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?


****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" wrote:

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" wrote in message
...
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,TRU E))

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" wrote:

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,TRU E))

HTH,

Conan Kelly




"Sondra" wrote in message
...
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.








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Want use results of Drop down list to look up a value Rob Van Pelt Excel Discussion (Misc queries) 3 February 24th 10 11:00 PM
how do I link a drop down list entry to a new drop down cell? lmunzen Excel Discussion (Misc queries) 1 August 15th 06 04:59 PM
Concatenate Results from a Validated Drop Down List Debra Dalgleish Excel Discussion (Misc queries) 1 April 27th 06 08:58 PM
multiple select from the drop down list in excel. list in one sheet and drop down in sriramus Excel Discussion (Misc queries) 5 October 27th 05 06:55 PM
How can I list the results of my macro without overwritng previous results? mattip Excel Programming 3 November 28th 03 04:45 AM


All times are GMT +1. The time now is 09:17 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"