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

S

Summer

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". :eek:) 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!
 
S

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". :eek:) 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!
 
S

Summer

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". :eek:) 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!
|
|
|
|
 
J

JMB

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

Summer

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

Summer

| 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!
 
M

Myrna Larson

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

Summer

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

JMB

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

Summer

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

Summer

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

If Sheet1 column is marked complete, display entire row on Sheet2? 1
Largest value in column A based on conditions in columns B and C 7
How to Shop Client delays in my ghantt chart so i can claim for delays 0
multiple work sheet data to link in one worksheet 6
Moving info from rows to columns 2
copy data from sheet1 to sheet but in varying positons 3
Bug in Excel? 2
linking to formula template on different tab 0

Top