Dynamic Range Names

  • Thread starter Thread starter JMay
  • Start date Start date
J

JMay

I'm reworking an application I did a few years ago. As I attempt to
"improve-it"
I'm not doing so good. I am using Data Validation Dropdown ListBoxes.
My Column A usage is OK.. In Column B I've made another Data Validation
Dropdown box which if "dependent" on the choice made in Column A, for
example:
It was working when things were as follows:

A1: Electrical Work B1: =Indirect(Substitute(A1," ","")) (to
eliminate space in rangename)
I have a range name - Electricalwork - with refers to box:
=Sheet2!$D$2:$D$6

I've tried changing things the refers to box to:
=Offset(Sheet2!$D$2,0,0,Counta(Sheet2!$D:$D),1)

but I'm getting message The Source currently evaluates to an error. Do
you wish to continue; Y/N
If I say Yes, when I click on B1 it is dead - Nothing is there (only the
drop-down arrow).

Any suggestions welcome..
Thanks in Advance.....

JMay
 
Debra:
Thanks for the reference; I've been to your Site numerous times for
assistance; It is a great resource. It helped me when I set up my app
originally, a year-or-so back.

As a side question, in working with the Defined Range Name Dialog Box,
specifically the Refers to: box -- When one has a lengthy formula (in the
box) it scrolls off to the right (out-of-sight) I often click on the small
icon which allows the box to e-x-p-a-n-d but it doesn't give you much more
viewing/edit area, meaning it still scrolls beyond the right border...
Using the arrow keys cause any existing formula "to-go-nuts" (creating
instant added field refs....%^%%) Anyway is there a way to (say by holding
down a combination of keys (Ctl, Alt, Shift) that will make this thing
behave normally?
TIA,
JMay
 
You're welcome, and thanks for letting me know that my site helped you.

To change the arrow key behaviour, click in the Refers To box,
and press the F2 key.
You can use the arrow keys to move through the Refers To box.
 
Bless you, Bless you, Bless you;
10 or 20 times over the past 2-3 years I've needed to know
what you just told me. ...the old F2 (edit mode) key, who'd a thought..
daaaaaa
Have a great day.
 
Back
Top