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". ) 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!
--
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". ) 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!
> --
> 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,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!

"JMB" <(E-Mail Removed)> wrote in message
news:8BF2F6AA-F2EE-4115-B870-(E-Mail Removed)...
| 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". ) 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!

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

"Summer" <(E-Mail Removed)> wrote in message
newsA0re.179$(E-Mail Removed)...
| 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!
|
| "JMB" <(E-Mail Removed)> wrote in message
| news:8BF2F6AA-F2EE-4115-B870-(E-Mail Removed)...
|| 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". ) 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!
|
|
|
|

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,FALSE)&"
> "&VLOOKUP(A11,INDIRECT("ClientAddress"),5,FALSE))
>
> --
> Summer
>
> "Summer" <(E-Mail Removed)> wrote in message
> newsA0re.179$(E-Mail Removed)...
> | 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!
> |
> | "JMB" <(E-Mail Removed)> wrote in message
> | news:8BF2F6AA-F2EE-4115-B870-(E-Mail Removed)...
> || 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". ) 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!
> |
> |
> |
> |
>
>
>

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,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!
>
> "JMB" <(E-Mail Removed)> wrote in message
> news:8BF2F6AA-F2EE-4115-B870-(E-Mail Removed)...
> | 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". ) 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!
>
>
>
>
>

"JMB" <(E-Mail Removed)> wrote in message
news:563E5372-3665-431A-9484-(E-Mail Removed)...
|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

snipped

"JMB" <(E-Mail Removed)> wrote in message
news:639A9E1A-0A3E-4078-BA90-(E-Mail Removed)...
| 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!
--
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" <(E-Mail Removed)>
wrote:

>"JMB" <(E-Mail Removed)> wrote in message
>news:563E5372-3665-431A-9484-(E-Mail Removed)...
>|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.

Hi Myrna,

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

"Myrna Larson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
| 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" <(E-Mail Removed)>
| wrote:
|
| >"JMB" <(E-Mail Removed)> wrote in message
| >news:563E5372-3665-431A-9484-(E-Mail Removed)...
| >|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.
|