Data Validation - Formatting
 

was able to use the Data Validation. However, my problem now is for the
address field. I have it so I have a chose a customer name. That pops up
their address, which I have in this format, 3345 Clearview Avenue, Anywhere
City, OH, all on one line. I need 4 lines. I am going to change the
customer name to a code I have to use, that needs to pop up the customer name
and the address. However, I need the format to look like this:

W2234
John Doe
3345 Clearview Avenue
Anywhere City, OH 79990

The current format is:

John Doe
3345 Clearview Avenue.Anywhere City, OH, 79990

Is this possible using the validation. I was looking at the Dynamic option
but little confused and not sure this will do what I want

Thanks for any help

Data Validation - Formatting
 

Format the cell with Wrap Text (FormatCells, Alignment tab)
Then, in the formula that returns the address, use CHAR(10) anywhere
that you want a line break. For example:

=IF(B5="","",VLOOKUP(B5,CustLookup,2,FALSE)&
CHAR(10) &VLOOKUP(B5,CustLookup,3,FALSE)&
CHAR(10) &VLOOKUP(B5,CustLookup,4,FALSE)&
CHAR(10)&VLOOKUP(B5,CustLookup,5,FALSE))

klafert wrote:
was able to use the Data Validation. However, my problem now is for the
address field. I have it so I have a chose a customer name. That pops up
their address, which I have in this format, 3345 Clearview Avenue, Anywhere
City, OH, all on one line. I need 4 lines. I am going to change the
customer name to a code I have to use, that needs to pop up the customer name
and the address. However, I need the format to look like this:

W2234
John Doe
3345 Clearview Avenue
Anywhere City, OH 79990

The current format is:

John Doe
3345 Clearview Avenue.Anywhere City, OH, 79990

Is this possible using the validation. I was looking at the Dynamic option
but little confused and not sure this will do what I want

Thanks for any help




--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

Data Validation - Formatting
 

I wasn't using a formula. I was using the data validation. In the same
workbook on two spreadsheet - one for the form and the second for the data
list. One column under the heading customer - I have the customer name in
column A. If the customer has more than one ship to address then I have the
customer name listed under Column A for each ship to address in column B. In
column B under the heading Address I have the address listed as: 412 Sever
Rd,Norcross,GA,30092 and in column D under the heading ShiptoName I have the
customer name listed again.

I followed these instructions from this site, guess you know this site:

http://www.contextures.com/xlDataVal13.html. I could send you the workbook
if that is allowed here. But I pretty much follow these instructions to the
T. And used the formula:
=OFFSET(CustomerStart,MATCH(B8,CustomerColumn,0)-1,1,COUNTIF(CustomerColumn,B8),1).
I not sure where you would like me to put the formula you posted in your
message. I have used the Vlook up a lot. Now that you gave me that ideal as
I typed this maybe I should put the data on the second spreadsheet and
separate the address in fields, which is not hard for me to do since I
exported it that way and just use the vlookup command. I really like the
data validation as I could choose the ship to name and pull up all address
associated with the customer.




"Debra Dalgleish" wrote:

Format the cell with Wrap Text (FormatCells, Alignment tab)
Then, in the formula that returns the address, use CHAR(10) anywhere
that you want a line break. For example:

=IF(B5="","",VLOOKUP(B5,CustLookup,2,FALSE)&
CHAR(10) &VLOOKUP(B5,CustLookup,3,FALSE)&
CHAR(10) &VLOOKUP(B5,CustLookup,4,FALSE)&
CHAR(10)&VLOOKUP(B5,CustLookup,5,FALSE))

klafert wrote:
was able to use the Data Validation. However, my problem now is for the
address field. I have it so I have a chose a customer name. That pops up
their address, which I have in this format, 3345 Clearview Avenue, Anywhere
City, OH, all on one line. I need 4 lines. I am going to change the
customer name to a code I have to use, that needs to pop up the customer name
and the address. However, I need the format to look like this:

W2234
John Doe
3345 Clearview Avenue
Anywhere City, OH 79990

The current format is:

John Doe
3345 Clearview Avenue.Anywhere City, OH, 79990

Is this possible using the validation. I was looking at the Dynamic option
but little confused and not sure this will do what I want

Thanks for any help




--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

Data Validation - Formatting
 

Thanks for explaining what you're doing, and yes, I know that site
pretty well. <g

On the data list sheet, you could add line breaks in the addresses
(click where you want the line break, and press Alt+Enter).
Then, on the form, format the address cells with wrap text.

klafert wrote:
I wasn't using a formula. I was using the data validation. In the same
workbook on two spreadsheet - one for the form and the second for the data
list. One column under the heading customer - I have the customer name in
column A. If the customer has more than one ship to address then I have the
customer name listed under Column A for each ship to address in column B. In
column B under the heading Address I have the address listed as: 412 Sever
Rd,Norcross,GA,30092 and in column D under the heading ShiptoName I have the
customer name listed again.

I followed these instructions from this site, guess you know this site:

http://www.contextures.com/xlDataVal13.html. I could send you the workbook
if that is allowed here. But I pretty much follow these instructions to the
T. And used the formula:
=OFFSET(CustomerStart,MATCH(B8,CustomerColumn,0)-1,1,COUNTIF(CustomerColumn,B8),1).
I not sure where you would like me to put the formula you posted in your
message. I have used the Vlook up a lot. Now that you gave me that ideal as
I typed this maybe I should put the data on the second spreadsheet and
separate the address in fields, which is not hard for me to do since I
exported it that way and just use the vlookup command. I really like the
data validation as I could choose the ship to name and pull up all address
associated with the customer.




"Debra Dalgleish" wrote:


Format the cell with Wrap Text (FormatCells, Alignment tab)
Then, in the formula that returns the address, use CHAR(10) anywhere
that you want a line break. For example:

=IF(B5="","",VLOOKUP(B5,CustLookup,2,FALSE)&
CHAR(10) &VLOOKUP(B5,CustLookup,3,FALSE)&
CHAR(10) &VLOOKUP(B5,CustLookup,4,FALSE)&
CHAR(10)&VLOOKUP(B5,CustLookup,5,FALSE))

klafert wrote:

was able to use the Data Validation. However, my problem now is for the
address field. I have it so I have a chose a customer name. That pops up
their address, which I have in this format, 3345 Clearview Avenue, Anywhere
City, OH, all on one line. I need 4 lines. I am going to change the
customer name to a code I have to use, that needs to pop up the customer name
and the address. However, I need the format to look like this:

W2234
John Doe
3345 Clearview Avenue
Anywhere City, OH 79990

The current format is:

John Doe
3345 Clearview Avenue.Anywhere City, OH, 79990

Is this possible using the validation. I was looking at the Dynamic option
but little confused and not sure this will do what I want

Thanks for any help




--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com





--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

Data Validation - Formatting
 

Thank you - that worked along with column width - thanks a bunch!!!

"Debra Dalgleish" wrote:

Thanks for explaining what you're doing, and yes, I know that site
pretty well. <g

On the data list sheet, you could add line breaks in the addresses
(click where you want the line break, and press Alt+Enter).
Then, on the form, format the address cells with wrap text.

klafert wrote:
I wasn't using a formula. I was using the data validation. In the same
workbook on two spreadsheet - one for the form and the second for the data
list. One column under the heading customer - I have the customer name in
column A. If the customer has more than one ship to address then I have the
customer name listed under Column A for each ship to address in column B. In
column B under the heading Address I have the address listed as: 412 Sever
Rd,Norcross,GA,30092 and in column D under the heading ShiptoName I have the
customer name listed again.

I followed these instructions from this site, guess you know this site:

http://www.contextures.com/xlDataVal13.html. I could send you the workbook
if that is allowed here. But I pretty much follow these instructions to the
T. And used the formula:
=OFFSET(CustomerStart,MATCH(B8,CustomerColumn,0)-1,1,COUNTIF(CustomerColumn,B8),1).
I not sure where you would like me to put the formula you posted in your
message. I have used the Vlook up a lot. Now that you gave me that ideal as
I typed this maybe I should put the data on the second spreadsheet and
separate the address in fields, which is not hard for me to do since I
exported it that way and just use the vlookup command. I really like the
data validation as I could choose the ship to name and pull up all address
associated with the customer.




"Debra Dalgleish" wrote:


Format the cell with Wrap Text (FormatCells, Alignment tab)
Then, in the formula that returns the address, use CHAR(10) anywhere
that you want a line break. For example:

=IF(B5="","",VLOOKUP(B5,CustLookup,2,FALSE)&
CHAR(10) &VLOOKUP(B5,CustLookup,3,FALSE)&
CHAR(10) &VLOOKUP(B5,CustLookup,4,FALSE)&
CHAR(10)&VLOOKUP(B5,CustLookup,5,FALSE))

klafert wrote:

was able to use the Data Validation. However, my problem now is for the
address field. I have it so I have a chose a customer name. That pops up
their address, which I have in this format, 3345 Clearview Avenue, Anywhere
City, OH, all on one line. I need 4 lines. I am going to change the
customer name to a code I have to use, that needs to pop up the customer name
and the address. However, I need the format to look like this:

W2234
John Doe
3345 Clearview Avenue
Anywhere City, OH 79990

The current format is:

John Doe
3345 Clearview Avenue.Anywhere City, OH, 79990

Is this possible using the validation. I was looking at the Dynamic option
but little confused and not sure this will do what I want

Thanks for any help




--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com





--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

Data Validation - Formatting
 

Your solution worked. Now I have one more question. I want the Phone number
to pop up in another area on the form. I assume that I have to put the phone
number in a separate column.

Example: I have a customer number I use to match and it pulls up the 3 Ship
To Address that works just fine, but after I choose the address if I want the
phone number associated with the Ship To address I have chosen to show up a
few lines down and not pop up the phone number for all 3 Ship To Address is
that possible?

"Debra Dalgleish" wrote:

Thanks for explaining what you're doing, and yes, I know that site
pretty well. <g

On the data list sheet, you could add line breaks in the addresses
(click where you want the line break, and press Alt+Enter).
Then, on the form, format the address cells with wrap text.

klafert wrote:
I wasn't using a formula. I was using the data validation. In the same
workbook on two spreadsheet - one for the form and the second for the data
list. One column under the heading customer - I have the customer name in
column A. If the customer has more than one ship to address then I have the
customer name listed under Column A for each ship to address in column B. In
column B under the heading Address I have the address listed as: 412 Sever
Rd,Norcross,GA,30092 and in column D under the heading ShiptoName I have the
customer name listed again.

I followed these instructions from this site, guess you know this site:

http://www.contextures.com/xlDataVal13.html. I could send you the workbook
if that is allowed here. But I pretty much follow these instructions to the
T. And used the formula:
=OFFSET(CustomerStart,MATCH(B8,CustomerColumn,0)-1,1,COUNTIF(CustomerColumn,B8),1).
I not sure where you would like me to put the formula you posted in your
message. I have used the Vlook up a lot. Now that you gave me that ideal as
I typed this maybe I should put the data on the second spreadsheet and
separate the address in fields, which is not hard for me to do since I
exported it that way and just use the vlookup command. I really like the
data validation as I could choose the ship to name and pull up all address
associated with the customer.




"Debra Dalgleish" wrote:


Format the cell with Wrap Text (FormatCells, Alignment tab)
Then, in the formula that returns the address, use CHAR(10) anywhere
that you want a line break. For example:

=IF(B5="","",VLOOKUP(B5,CustLookup,2,FALSE)&
CHAR(10) &VLOOKUP(B5,CustLookup,3,FALSE)&
CHAR(10) &VLOOKUP(B5,CustLookup,4,FALSE)&
CHAR(10)&VLOOKUP(B5,CustLookup,5,FALSE))

klafert wrote:

was able to use the Data Validation. However, my problem now is for the
address field. I have it so I have a chose a customer name. That pops up
their address, which I have in this format, 3345 Clearview Avenue, Anywhere
City, OH, all on one line. I need 4 lines. I am going to change the
customer name to a code I have to use, that needs to pop up the customer name
and the address. However, I need the format to look like this:

W2234
John Doe
3345 Clearview Avenue
Anywhere City, OH 79990

The current format is:

John Doe
3345 Clearview Avenue.Anywhere City, OH, 79990

Is this possible using the validation. I was looking at the Dynamic option
but little confused and not sure this will do what I want

Thanks for any help




--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com





--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

Data Validation - Formatting
 

Right now I use the same customer Id for all related Ship To address for this
one customer. I know that if I use a different code like

W224A = SHIP TO ADDRESS 1
W224B = SHIP TO ADDRESS 1
W224C = SHIP TO ADDRESS 1

This would work and pop up the phone number assocatied with each Ship To
Address for each code but however I want the display to be W224 and not show
W224A, W224B, ETC.



"klafert" wrote:

Your solution worked. Now I have one more question. I want the Phone number
to pop up in another area on the form. I assume that I have to put the phone
number in a separate column.

Example: I have a customer number I use to match and it pulls up the 3 Ship
To Address that works just fine, but after I choose the address if I want the
phone number associated with the Ship To address I have chosen to show up a
few lines down and not pop up the phone number for all 3 Ship To Address is
that possible?

"Debra Dalgleish" wrote:

Thanks for explaining what you're doing, and yes, I know that site
pretty well. <g

On the data list sheet, you could add line breaks in the addresses
(click where you want the line break, and press Alt+Enter).
Then, on the form, format the address cells with wrap text.

klafert wrote:
I wasn't using a formula. I was using the data validation. In the same
workbook on two spreadsheet - one for the form and the second for the data
list. One column under the heading customer - I have the customer name in
column A. If the customer has more than one ship to address then I have the
customer name listed under Column A for each ship to address in column B. In
column B under the heading Address I have the address listed as: 412 Sever
Rd,Norcross,GA,30092 and in column D under the heading ShiptoName I have the
customer name listed again.

I followed these instructions from this site, guess you know this site:

http://www.contextures.com/xlDataVal13.html. I could send you the workbook
if that is allowed here. But I pretty much follow these instructions to the
T. And used the formula:
=OFFSET(CustomerStart,MATCH(B8,CustomerColumn,0)-1,1,COUNTIF(CustomerColumn,B8),1).
I not sure where you would like me to put the formula you posted in your
message. I have used the Vlook up a lot. Now that you gave me that ideal as
I typed this maybe I should put the data on the second spreadsheet and
separate the address in fields, which is not hard for me to do since I
exported it that way and just use the vlookup command. I really like the
data validation as I could choose the ship to name and pull up all address
associated with the customer.




"Debra Dalgleish" wrote:


Format the cell with Wrap Text (FormatCells, Alignment tab)
Then, in the formula that returns the address, use CHAR(10) anywhere
that you want a line break. For example:

=IF(B5="","",VLOOKUP(B5,CustLookup,2,FALSE)&
CHAR(10) &VLOOKUP(B5,CustLookup,3,FALSE)&
CHAR(10) &VLOOKUP(B5,CustLookup,4,FALSE)&
CHAR(10)&VLOOKUP(B5,CustLookup,5,FALSE))

klafert wrote:

was able to use the Data Validation. However, my problem now is for the
address field. I have it so I have a chose a customer name. That pops up
their address, which I have in this format, 3345 Clearview Avenue, Anywhere
City, OH, all on one line. I need 4 lines. I am going to change the
customer name to a code I have to use, that needs to pop up the customer name
and the address. However, I need the format to look like this:

W2234
John Doe
3345 Clearview Avenue
Anywhere City, OH 79990

The current format is:

John Doe
3345 Clearview Avenue.Anywhere City, OH, 79990

Is this possible using the validation. I was looking at the Dynamic option
but little confused and not sure this will do what I want

Thanks for any help




--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com





--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com