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". :o) 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("ClientAddre ss"),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!
--
Summer (no valid email) and (still Googling)
Thought ~
If you stop to kick at every dog that barks at you, you'll never get very
far.

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". :o) 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("ClientAddre ss"),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!
--
Summer (no valid email) and (still Googling)
Thought ~
If you stop to kick at every dog that barks at you, you'll never get very
far.

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,FALS E)&
" "&VLOOKUP(A11,INDIRECT("ClientAddress"),5,FALS E)

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!

"JMB" wrote in message
...
| 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". :o) 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("ClientAddre ss"),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!

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,FALS E)&"
"&VLOOKUP(A11,INDIRECT("ClientAddress"),5,FALS E))

--
Summer

"Summer" wrote in message
...
| 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,FALS E)&
| " "&VLOOKUP(A11,INDIRECT("ClientAddress"),5,FALS E)
|
| 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!
|
| "JMB" wrote in message
| ...
|| 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". :o) 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("ClientAddre ss"),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!
|
|
|
|

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.



"Summer" wrote:

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,FALS E)&"
"&VLOOKUP(A11,INDIRECT("ClientAddress"),5,FALS E))

--
Summer

"Summer" wrote in message
...
| 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,FALS E)&
| " "&VLOOKUP(A11,INDIRECT("ClientAddress"),5,FALS E)
|
| 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!
|
| "JMB" wrote in message
| ...
|| 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". :o) 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("ClientAddre ss"),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!
|
|
|
|

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



"Summer" wrote:

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,FALS E)&
" "&VLOOKUP(A11,INDIRECT("ClientAddress"),5,FALS E)

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!

"JMB" wrote in message
...
| 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". :o) 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("ClientAddre ss"),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!

"JMB" wrote in message
...
|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_l ookup

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 we

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 a

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

snipped

"JMB" wrote in message
...
| 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,FALS E)&
" "&VLOOKUP(A11,INDIRECT("ClientAddress"),5,FALS E))

(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!
--
Summer

snipped

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

"JMB" wrote in message
...
|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_l ookup

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 we

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 a

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.

Hi Myrna,

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

"Myrna Larson" wrote in message
...
| 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"
| wrote:
|
| "JMB" wrote in message
| ...
| |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_l ookup
|
| 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 we
|
| 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 a
|
| 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.
|

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?
::
--
Summer

"Myrna Larson" wrote in message
...
| 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"
| wrote:
|
| "JMB" wrote in message
| ...
| |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_l ookup
|
| 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 we
|
| 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 a
|
| 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.
|

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)

"JMB" wrote in message
...
| 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?
| ::
| --
| Summer
|
| "Myrna Larson" wrote in message
| ...
| | 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"

| | wrote:
| |
| | "JMB" wrote in message
| | ...
| | |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_l ookup
| |
| | 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 we
| |
| | 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 a
| |
| | 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.
| |
|
|
|
|

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)

"JMB" wrote in message
...
| 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?
| ::
| --
| Summer
|
| "Myrna Larson" wrote in message
| ...
| | 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"

| | wrote:
| |
| | "JMB" wrote in message
| | ...
| | |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_l ookup
| |
| | 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 we
| |
| | 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 a
| |
| | 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.
| |
|
|
|
|