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