On Jun 16, 1:19*am, GS wrote:
prkhan56 submitted this idea :





On Jun 16, 12:01*am, GS wrote:
Corrections:

* Cell A1="City", Cell B1="Town", Cell C1="State"
* Define A1 as...
* * Name="'Sheet name'!City_Hdr",
* * RefersTo="=$A$1"

* Define the list under "City_Hdr" as...
* * Name="'Sheet name'!City",
* * RefersTo="=OFFSET(City_Hdr,1,0,COUNTA($A:$A)-1,1)

* * Define B1 as...

* * Name="'Sheet name'!City_Hdr",
* * RefersTo="=$B$1"

* Define the list under "Town_Hdr" as...
* * Name="'Sheet name'!Town",
* * RefersTo="=OFFSET(Town_Hdr,1,0,COUNTA($B:$B)-1,1)

* * Define C1 as...

* * Name="'Sheet name'!State_Hdr",
* * RefersTo="=$C$1"

* Define the list under "State_Hdr" as...
* * Name="'Sheet name'!State",
* * RefersTo="=OFFSET(State_Hdr,1,0,COUNTA($C:$C)-1,1)

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Hi Gary,
Thanks for your suggestion.
Claus has provided the code

Ok, but note that Claus's very nice code doesn't make the ranges
dynamic and so it must be run every time new data is added. Debra's
suggestion plus my suggestion results in the ranges automatically
adjusting for data being added or removed.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

Hi Gary,
Thanks for your time and suggestion.