[mso] Re: Can I do an INDIRECT + x columns?
- From: "Ray Blake" <ray@xxxxxxxxx>
- To: <mso@xxxxxxxxxxxxx>
- Date: Wed, 12 May 2004 21:41:46 +0100
Yes, Robert, you can. Sort of. First I'll show you how to do what you're
asking, but then I'll suggest an easier way, if you don't mind.
The way to do the 'jump 50 to the right' thing is to use the OFFSET
command, something like this:
=OFFSET(INDIRECT(H4),0,50)
But, this will return a range equal is size to the named range, and I
would guess your list of towns would be longer than your list of
counties. There are ways around this, using a dynamic range selection
method, but they're pretty horrid, to be honest.
My preferred method would be to name all these extra 50 ranges, but to
name them by adding one character consistently to the range name for the
county ranges. For instance, if the county ranges include:
New_Jersey
Ohio
- the corresponding town ranges are:
New_JerseyT
OhioT
To reference these new regions in your data validation, you simply need
the formula:
=INDIRECT(H4 & "T")
Ray
------------------------------------
GR Business Process Solutions
Ray Blake
Head of Software Design
ray@xxxxxxxxx
Braedon
Newell Road
Hemel Hempstead
Herts HP3 9PD
tel: 01442 396518
fax: 01442 389353
www.grbps.com
------------------------------------
-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx] On
Behalf Of Robert Carneal
Sent: 12 May 2004 21:25
To: mso@xxxxxxxxxxxxx
Subject: [mso] Can I do an INDIRECT + x columns?
I am trying to make it easier for the user to enter data on a census
form,
and by restricting what the user can enter. I allow the user to select a
state in cell H4. On another sheet, I have the counties for all 50
states,
and named them as ranges. So selecting North_Carolina will get you a
listing of counties in North Carolina. This part works. In data
validation,
I entered: =INDIRECT(H4)
On another sheet, I have towns, townships, villages, & cities for each
state. This information is on the same sheet, and I was hoping to use
indirect again. Could I enter some form of INDIRECT that will go the
same
column as that state's counties, but move over to the right 50 columns?
Sort of like:
=INDIRECT(INDIRECT(H4),50) {Should this move 50 columns to the
right?}
??
I am not sure I understand INDIRECT completely. One of my books shows an
example nesting three INDIRECTs.
I have a temp work around by making the user select the state twice, one
to
get listing of counties and the 2nd time to get a listing of cities. But
that is not safe, the user could select Virginia and get Virginia
counties
and accidentally select West Virginia and be seeing West Virginian towns
instead of Virginia towns. So I would very much like to idiot-proof this
a
little.
I would like to cut the workbook down the three sheets, the census,
counties, and cities. Can I upload this workbook and if anyone solve my
lookup for cities, tell me how please? I really am at a loss.
I don't know if I just go ahead and upload, do I need someone's
permission,
or?
Thank you.
Robert
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, send an email to
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the quotes) in
the subject line.
Or, visit the group's homepage and use the dropdown menu. This will also allow
you to change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with
instructions. Once you are a member of the files group, you can go here to
upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************
Other related posts:
