Combine 3 Columns on Sheet2 to Display in 1 Column on Sheet1

Summer · Jun 12, 2005

Hi,

This is a novel, so bear with me, please. So much work for what is probably
a very simple answer, but I figure too much info is better than "twenty
questions". :blush:

) Think I'll frame this post - it took so long to write.

First of all, I THANK YOU wonderful people for all your unselfish work here.
Your informative websites and continued support in the NGs has made learning
Excel a much more pleasurable experience.

Recently, I have been busy studying and practicing at Debra Dalgleish's
website http://www.contextures.com . Great site.

So far, I have a Workbook with 3 sheet tabs: Invoice, Clients, Services, in
that order. Eventually I hope to create a summary page, etc. But, right now
I am working on a relationship/formula between the Invoice and Clients
sheets. I have Googled a-plenty. Found a bunch of cool stuff to play with
later...anyway...

This Workbook will be for one user on a stand-alone computer utilizing WinXP
Pro with Excel 2003.
------------------------------------------------------------
(Hope my terminology is correct and everything lines up so you can make
sense of it)
Clients sheet looks like:

___A____________B______________C________D_______E____F
1 CLIENTS

2 Client Name_____Company Name___Street_____City____State__Zip
3 John Thomas____ABC, Inc.________103 ST____Any____CA___36789
4...

Named ranges of:
ClientAddress- refers to: =Clients!$B$3:$F$25
Clients- refers to: =Clients!$B$3:$B$25
Services- refers to: =Services!$A$2:$A$7 (not applicable to this post)
------------------------------------------------------------
Invoice sheet looks like:

___A______etc...
1
2
3...
11 ABC, Inc.
12 103 ST
13 Any, CA 36789
etc...
------------------------------------------------------------

Cell A11 contains a Data Validation list to select the client's company name
from the Clients sheet. Validation Criteria of Allow: List, Source:
=Clients. This works fine.

Cell A12 contains this formula:

=IF(A11="","",VLOOKUP(A11,INDIRECT("ClientAddress"),2,FALSE))

This also works fine. It displays the corresponding Street from column C for
each client.

Okay, here's where my changes start.

Cell A13 *used* to contain this formula:

=IF(A11="","",VLOOKUP(A11,INDIRECT("ClientAddress"),3,FALSE))

because previously I had the City, State Zip all in one column (D) on the
Clients sheet. This, too, worked fine but seemed clunky to me (not best
practice?).

So, I found and followed Debra's wonderful instructions at
http://www.contextures.com/xlCombine01.html for combining cells. Thanks
Debra!

Cell A13 *then* contained this formula:

=Clients!D3&","&" "&Clients!E3&" "&Clients!F3

This displayed the City, St Zip nicely together on the Invoice - BUT for
only that one client in row 3, John Thomas! Of course, it needs to work for
the whole list of clients. My temporary(?) workaround:

In the Clients sheet:

G3=D3&","&" "&E3&" "&F3 (dragged down to row 25)

Edited the named range ClientAddress to include the G column.

In the Invoice sheet:

A13=IF(A11="","",VLOOKUP(A11,INDIRECT("ClientAddress"),6,FALSE))

Which, basically, puts me right back where I was. I'm thinking the way I had
it the first time was neater (City, State Zip all in Col D)

Is there a better way, other than the two I've found, that is considered
"best practice"?

Thanks in advance for your input!

Guest · Jun 12, 2005

One possibility:

IF(A11="","",VLOOKUP(City)&", " VLOOKUP(State)&" "&VLOOKUP(Zip))

Summer · Jun 12, 2005

Thanks JMB!

Could not get your formula to work. But I like how streamlined it is. And I
learned from you that there can be more than one VLOOKUP in a formula! Took
a closer look at VLOOKUP at Debra's website and came up with this working
solution (after a couple of hours of trial and error this afternoon):

=IF(A11="","",VLOOKUP(A11,INDIRECT("ClientAddress"),3,FALSE))&","&
" "&VLOOKUP(A11,INDIRECT("ClientAddress"),4,FALSE)&
" "&VLOOKUP(A11,INDIRECT("ClientAddress"),5,FALSE)

I'd still like a more streamlined version. Think I'll try out yours again to
see if I can come up with something shorter than the above. Thanks again for
your help in pointing me in the right direction!

| One possibility:
|
| IF(A11="","",VLOOKUP(City)&", " VLOOKUP(State)&" "&VLOOKUP(Zip))
|
|
|
|
| "Summer" wrote:
|
| > Hi,
| >
| > This is a novel, so bear with me, please. So much work for what is
probably
| > a very simple answer, but I figure too much info is better than "twenty
| > questions". :blush:

Summer · Jun 13, 2005

Um. This works, too. (I placed a parenthesis incorrectly in the other one):

=IF(A11="","",VLOOKUP(A11,INDIRECT("ClientAddress"),3,FALSE)&","&"
"&VLOOKUP(A11,INDIRECT("ClientAddress"),4,FALSE)&"
"&VLOOKUP(A11,INDIRECT("ClientAddress"),5,FALSE))

--
Summer

| Thanks JMB!
|
| Could not get your formula to work. But I like how streamlined it is. And
I
| learned from you that there can be more than one VLOOKUP in a formula!
Took
| a closer look at VLOOKUP at Debra's website and came up with this working
| solution (after a couple of hours of trial and error this afternoon):
|
| =IF(A11="","",VLOOKUP(A11,INDIRECT("ClientAddress"),3,FALSE))&","&
| " "&VLOOKUP(A11,INDIRECT("ClientAddress"),4,FALSE)&
| " "&VLOOKUP(A11,INDIRECT("ClientAddress"),5,FALSE)
|
| I'd still like a more streamlined version. Think I'll try out yours again
to
| see if I can come up with something shorter than the above. Thanks again
for
| your help in pointing me in the right direction!
|
| || One possibility:
||
|| IF(A11="","",VLOOKUP(City)&", " VLOOKUP(State)&" "&VLOOKUP(Zip))
||
||
||
||
|| "Summer" wrote:
||
|| > Hi,
|| >
|| > This is a novel, so bear with me, please. So much work for what is
| probably
|| > a very simple answer, but I figure too much info is better than "twenty
|| > questions". :blush:

) Think I'll frame this post - it took so long to write.
|| >
|| > First of all, I THANK YOU wonderful people for all your unselfish work
| here.
|| > Your informative websites and continued support in the NGs has made
| learning
|| > Excel a much more pleasurable experience.
|| >
|| > Recently, I have been busy studying and practicing at Debra Dalgleish's
|| > website http://www.contextures.com . Great site.
|| >
|| > So far, I have a Workbook with 3 sheet tabs: Invoice, Clients,
Services,
| in
|| > that order. Eventually I hope to create a summary page, etc. But, right
| now
|| > I am working on a relationship/formula between the Invoice and Clients
|| > sheets. I have Googled a-plenty. Found a bunch of cool stuff to play
| with
|| > later...anyway...
|| >
|| > This Workbook will be for one user on a stand-alone computer utilizing
| WinXP
|| > Pro with Excel 2003.
|| > ------------------------------------------------------------
|| > (Hope my terminology is correct and everything lines up so you can make
|| > sense of it)
|| > Clients sheet looks like:
|| >
|| > ___A____________B______________C________D_______E____F
|| > 1 CLIENTS
|| >
|| > 2 Client Name_____Company Name___Street_____City____State__Zip
|| > 3 John Thomas____ABC, Inc.________103 ST____Any____CA___36789
|| > 4...
|| >
|| > Named ranges of:
|| > ClientAddress- refers to: =Clients!$B$3:$F$25
|| > Clients- refers to: =Clients!$B$3:$B$25
|| > Services- refers to: =Services!$A$2:$A$7 (not applicable to this post)
|| > ------------------------------------------------------------
|| > Invoice sheet looks like:
|| >
|| > ___A______etc...
|| > 1
|| > 2
|| > 3...
|| > 11 ABC, Inc.
|| > 12 103 ST
|| > 13 Any, CA 36789
|| > etc...
|| > ------------------------------------------------------------
|| >
|| > Cell A11 contains a Data Validation list to select the client's company
| name
|| > from the Clients sheet. Validation Criteria of Allow: List, Source:
|| > =Clients. This works fine.
|| >
|| > Cell A12 contains this formula:
|| >
|| > =IF(A11="","",VLOOKUP(A11,INDIRECT("ClientAddress"),2,FALSE))
|| >
|| > This also works fine. It displays the corresponding Street from column
C
| for
|| > each client.
|| >
|| > Okay, here's where my changes start.
|| >
|| > Cell A13 *used* to contain this formula:
|| >
|| > =IF(A11="","",VLOOKUP(A11,INDIRECT("ClientAddress"),3,FALSE))
|| >
|| > because previously I had the City, State Zip all in one column (D) on
| the
|| > Clients sheet. This, too, worked fine but seemed clunky to me (not best
|| > practice?).
|| >
|| > So, I found and followed Debra's wonderful instructions at
|| > http://www.contextures.com/xlCombine01.html for combining cells. Thanks
|| > Debra!
|| >
|| > Cell A13 *then* contained this formula:
|| >
|| > =Clients!D3&","&" "&Clients!E3&" "&Clients!F3
|| >
|| > This displayed the City, St Zip nicely together on the Invoice - BUT
for
|| > only that one client in row 3, John Thomas! Of course, it needs to work
| for
|| > the whole list of clients. My temporary(?) workaround:
|| >
|| > In the Clients sheet:
|| >
|| > G3=D3&","&" "&E3&" "&F3 (dragged down to row 25)
|| >
|| > Edited the named range ClientAddress to include the G column.
|| >
|| > In the Invoice sheet:
|| >
|| > A13=IF(A11="","",VLOOKUP(A11,INDIRECT("ClientAddress"),6,FALSE))
|| >
|| > Which, basically, puts me right back where I was. I'm thinking the way
I
| had
|| > it the first time was neater (City, State Zip all in Col D)
|| >
|| > Is there a better way, other than the two I've found, that is
considered
|| > "best practice"?
|| >
|| > Thanks in advance for your input!
|
|
|
|

Guest · Jun 13, 2005

very good.

also, I believe you can take out the Indirect("ClientAddress") and just use
ClientAddress (w/o quotes) since it is a named range.

Guest · Jun 13, 2005

I think the problem is I left out the & right before VLOOKUP(State).

Summer · Jun 13, 2005

|I think the problem is I left out the & right before VLOOKUP(State).

Hi,

I still could not get this one to work with your suggested revision. When I
added the ampersand, the formula still returned "You've entered too few
arguments for this function.". (I also added the equals sign and the
specified space after the specified comma):

Your formula as originally posted:

IF(A11="","",VLOOKUP(City)&"," VLOOKUP(State)&" "&VLOOKUP(Zip))

Your formula with our revisions:

=IF(A11="","",VLOOKUP(City)&","&" "&VLOOKUP(State)&" "&VLOOKUP(Zip))

Does not work. Sorry.
----------------------
My understanding is that VLOOKUP requires four arguments. For example:

lookup_value______ table_array__________col_index_num_________range_lookup

A11_____________ClientAddress________3___________________FALSE (to specify
exact match)

The arguments in the whole formula would read something like: IF A11 is
blank, then leave blank, otherwise IF A11 = company name, THEN lookup named
range of ClientAddress and return value in column 3, must be exact match.
----------------
I WAS able to get your formula to work if I gave City, State and Zip EACH
the same range as ClientAddress and included all four arguments for each
VLOOKUP function.

The original named ranges were:

ClientAddress- refers to: =Clients!$B$3:$F$25
Clients- refers to: =Clients!$B$3:$B$25
Services- refers to: =Services!$A$2:$A$7 (ignore - not appl. here)

Added named ranges are:

City- refers to: =Clients!$B$3:$F$25
State- refers to: =Clients!$B$3:$F$25
Zip-refers to: =Clients!$B$3:$F$25

There are no headers included in the ranges. I wonder if that would that
make a difference. I'll have to check this out.

The final result with all revisions:

=IF(A11="","",VLOOKUP(A11,City,3,FALSE)&","&" "
&VLOOKUP(A11,State,4,FALSE)&" "
&VLOOKUP(A11,Zip,5,FALSE))

(This works! 106 characters long compared to the first revision of 133 char.
long compared to the original of 169 char. long )

Thanks for all your helpful suggestions! I learned a lot from our exchange.

Summer · Jun 13, 2005

| very good.
|
| also, I believe you can take out the Indirect("ClientAddress") and just
use
| ClientAddress (w/o quotes) since it is a named range.
|

Hi again JMB,

Thanks for staying with me here. I was able to get your above suggestion to
work.

Took out the INDIRECT, the parentheses and the quotation marks from my
solution below:

=IF(A11="","",VLOOKUP(A11,INDIRECT("ClientAddress"),3,FALSE)&","&
" "&VLOOKUP(A11,INDIRECT("ClientAddress"),4,FALSE)&
" "&VLOOKUP(A11,INDIRECT("ClientAddress"),5,FALSE))

(169 characters long) and came up with this slightly shorter version:

=IF(A11="","",VLOOKUP(A11,ClientAddress,3,FALSE)&","&
" "&VLOOKUP(A11,ClientAddress,4,FALSE)&
" "&VLOOKUP(A11,ClientAddress,5,FALSE))

(133 characters long)

See my next post for the answer to your other posted suggestion/fix. Thanks
again!

Myrna Larson · Jun 13, 2005

If you check Help for VLOOKUP, you'll see that it requires at least 3 and
possibly 4 arguments. You've supplied only 1.

I suggest you learn to use this function, as it's very useful.

Summer · Jun 13, 2005

Hi Myrna,

Thanks for responding. To whom are you making this suggestion?
: :confused:

Guest · Jun 14, 2005

My apologies.

I just shorthanded VLookup(City).

VLOOKUP(A11,ClientAddress,3,FALSE) I believe is the function in your
previous post that looks up the City. Since it appeared you were already
using VLookup correctly (with 4 arguments) I didn't retype the entire
function.

Summer · Jun 14, 2005

JMB,

Not a problem! I get it now (duh, next time I will try to remember that
answers to some questions may be abbreviated - never occurred to me before).
I've only been learning (self-teaching) Excel for about 1-3 weeks. Your
input has helped me to delve deeper into Excel functions and learn some new
things!. I Thank You for your help! It all worked out in the end.
--
Summer (no valid email)

| My apologies.
|
| I just shorthanded VLookup(City).
|
| VLOOKUP(A11,ClientAddress,3,FALSE) I believe is the function in your
| previous post that looks up the City. Since it appeared you were already
| using VLookup correctly (with 4 arguments) I didn't retype the entire
| function.
|
|
|
|
| "Summer" wrote:
|
| > Hi Myrna,
| >
| > Thanks for responding. To whom are you making this suggestion?
| > : :confused:

:
| > --
| > Summer
| >
| > | > | If you check Help for VLOOKUP, you'll see that it requires at least 3
and
| > | possibly 4 arguments. You've supplied only 1.
| > |
| > | I suggest you learn to use this function, as it's very useful.
| > |
| > |
| > | On Mon, 13 Jun 2005 15:55:19 GMT, "Summer"
<[email protected]>
| > | wrote:
| > |
| > | >| > | >|I think the problem is I left out the & right before VLOOKUP(State).
| > | >
| > | >Hi,
| > | >
| > | >I still could not get this one to work with your suggested revision.
When
| > I
| > | >added the ampersand, the formula still returned "You've entered too
few
| > | >arguments for this function.". (I also added the equals sign and the
| > | >specified space after the specified comma):
| > | >
| > | >Your formula as originally posted:
| > | >
| > | >IF(A11="","",VLOOKUP(City)&"," VLOOKUP(State)&" "&VLOOKUP(Zip))
| > | >
| > | >Your formula with our revisions:
| > | >
| > | >=IF(A11="","",VLOOKUP(City)&","&" "&VLOOKUP(State)&" "&VLOOKUP(Zip))
| > | >
| > | >Does not work. Sorry.
| > | >----------------------
| > | >My understanding is that VLOOKUP requires four arguments. For
example:
| > | >
| > | >lookup_value______
| > table_array__________col_index_num_________range_lookup
| > | >
| > | >A11_____________ClientAddress________3___________________FALSE (to
| > specify
| > | >exact match)
| > | >
| > | >The arguments in the whole formula would read something like: IF A11
is
| > | >blank, then leave blank, otherwise IF A11 = company name, THEN lookup
| > named
| > | >range of ClientAddress and return value in column 3, must be exact
match.
| > | >----------------
| > | >I WAS able to get your formula to work if I gave City, State and Zip
EACH
| > | >the same range as ClientAddress and included all four arguments for
each
| > | >VLOOKUP function.
| > | >
| > | >The original named ranges were:
| > | >
| > | >ClientAddress- refers to: =Clients!$B$3:$F$25
| > | >Clients- refers to: =Clients!$B$3:$B$25
| > | >Services- refers to: =Services!$A$2:$A$7 (ignore - not appl. here)
| > | >
| > | >Added named ranges are:
| > | >
| > | >City- refers to: =Clients!$B$3:$F$25
| > | >State- refers to: =Clients!$B$3:$F$25
| > | >Zip-refers to: =Clients!$B$3:$F$25
| > | >
| > | >
| > | >There are no headers included in the ranges. I wonder if that would
that
| > | >make a difference. I'll have to check this out.
| > | >
| > | >The final result with all revisions:
| > | >
| > | >=IF(A11="","",VLOOKUP(A11,City,3,FALSE)&","&" "
| > | >&VLOOKUP(A11,State,4,FALSE)&" "
| > | >&VLOOKUP(A11,Zip,5,FALSE))
| > | >
| > | >(This works! 106 characters long compared to the first revision of
133
| > char.
| > | >long compared to the original of 169 char. long )
| > | >
| > | >Thanks for all your helpful suggestions! I learned a lot from our
| > exchange.
| > |
| >
| >
| >
| >

Summer · Jun 14, 2005

JMB and all,

Sorry, forgot to add the final solution to my last post (so others who are
learning will not get confused - I hope).

=IF(A11="","",VLOOKUP(A11,ClientAddress,3,FALSE)&","&"
"&VLOOKUP(A11,ClientAddress,4,FALSE)&" "&VLOOKUP(A11,ClientAddress,5,FALSE))

Where 3 denotes the column number on sheet2 (Clients) for City; 4 denotes
the column number for State; and 5 denotes the column number for Zip.

I removed the extra named ranges I had (ahem) created previously since they
aren't necessary: City, State Zip.

I seem to know just enough to be dangerous, huh?
--
Summer (no valid email)

| My apologies.
|
| I just shorthanded VLookup(City).
|
| VLOOKUP(A11,ClientAddress,3,FALSE) I believe is the function in your
| previous post that looks up the City. Since it appeared you were already
| using VLookup correctly (with 4 arguments) I didn't retype the entire
| function.
|
|
|
|
| "Summer" wrote:
|
| > Hi Myrna,
| >
| > Thanks for responding. To whom are you making this suggestion?
| > : :confused:

:
| > --
| > Summer
| >
| > | > | If you check Help for VLOOKUP, you'll see that it requires at least 3
and
| > | possibly 4 arguments. You've supplied only 1.
| > |
| > | I suggest you learn to use this function, as it's very useful.
| > |
| > |
| > | On Mon, 13 Jun 2005 15:55:19 GMT, "Summer"
<[email protected]>
| > | wrote:
| > |
| > | >| > | >|I think the problem is I left out the & right before VLOOKUP(State).
| > | >
| > | >Hi,
| > | >
| > | >I still could not get this one to work with your suggested revision.
When
| > I
| > | >added the ampersand, the formula still returned "You've entered too
few
| > | >arguments for this function.". (I also added the equals sign and the
| > | >specified space after the specified comma):
| > | >
| > | >Your formula as originally posted:
| > | >
| > | >IF(A11="","",VLOOKUP(City)&"," VLOOKUP(State)&" "&VLOOKUP(Zip))
| > | >
| > | >Your formula with our revisions:
| > | >
| > | >=IF(A11="","",VLOOKUP(City)&","&" "&VLOOKUP(State)&" "&VLOOKUP(Zip))
| > | >
| > | >Does not work. Sorry.
| > | >----------------------
| > | >My understanding is that VLOOKUP requires four arguments. For
example:
| > | >
| > | >lookup_value______
| > table_array__________col_index_num_________range_lookup
| > | >
| > | >A11_____________ClientAddress________3___________________FALSE (to
| > specify
| > | >exact match)
| > | >
| > | >The arguments in the whole formula would read something like: IF A11
is
| > | >blank, then leave blank, otherwise IF A11 = company name, THEN lookup
| > named
| > | >range of ClientAddress and return value in column 3, must be exact
match.
| > | >----------------
| > | >I WAS able to get your formula to work if I gave City, State and Zip
EACH
| > | >the same range as ClientAddress and included all four arguments for
each
| > | >VLOOKUP function.
| > | >
| > | >The original named ranges were:
| > | >
| > | >ClientAddress- refers to: =Clients!$B$3:$F$25
| > | >Clients- refers to: =Clients!$B$3:$B$25
| > | >Services- refers to: =Services!$A$2:$A$7 (ignore - not appl. here)
| > | >
| > | >Added named ranges are:
| > | >
| > | >City- refers to: =Clients!$B$3:$F$25
| > | >State- refers to: =Clients!$B$3:$F$25
| > | >Zip-refers to: =Clients!$B$3:$F$25
| > | >
| > | >
| > | >There are no headers included in the ranges. I wonder if that would
that
| > | >make a difference. I'll have to check this out.
| > | >
| > | >The final result with all revisions:
| > | >
| > | >=IF(A11="","",VLOOKUP(A11,City,3,FALSE)&","&" "
| > | >&VLOOKUP(A11,State,4,FALSE)&" "
| > | >&VLOOKUP(A11,Zip,5,FALSE))
| > | >
| > | >(This works! 106 characters long compared to the first revision of
133
| > char.
| > | >long compared to the original of 169 char. long )
| > | >
| > | >Thanks for all your helpful suggestions! I learned a lot from our
| > exchange.
| > |
| >
| >
| >
| >

If Sheet1 column is marked complete, display entire row on Sheet2?	1	Oct 12, 2007
another text to column problem	11	May 4, 2005
Splitting Text from single cell in column across multiple Columns	3	Mar 12, 2010
First column of range in VLOOKUP contains formula	2	Feb 4, 2010
How to select a cell from another work sheet base on a formula	1	Aug 6, 2004
need formula to count entries on each of 3 sheets?	1	Sep 24, 2004
how do copy "sheet1!A1+1 in sheet2 to sheet 3 and get "sheet2!A1+	5	Apr 16, 2007
VLookup Column Reference - can it increment when copying to subseq	3	Apr 15, 2008

Combine 3 Columns on Sheet2 to Display in 1 Column on Sheet1

Summer

Guest

Summer

Summer

Guest

Guest

Summer

Summer

Myrna Larson

Summer

Guest

Summer

Summer

Ask a Question

Similar Threads