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.