Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Want use results of Drop down list to look up a value | Excel Discussion (Misc queries) | |||
how do I link a drop down list entry to a new drop down cell? | Excel Discussion (Misc queries) | |||
Concatenate Results from a Validated Drop Down List | Excel Discussion (Misc queries) | |||
multiple select from the drop down list in excel. list in one sheet and drop down in | Excel Discussion (Misc queries) | |||
How can I list the results of my macro without overwritng previous results? | Excel Programming |