Help with drop-down box creation

  • Follow


I have a large spreadsheet of case manager activity.  Each case manager has a 
certain number of rows and columns for his/her information (case manager A 
uses the area A3:G43, case manager B uses H3:N43, etc.).  There are 13 case 
manager areas in group 1 (rows 1-43), 13 in group 2 (rows 44-86), 13 in group 
3 (rows 87-129), 4 sets in group 4 (rows 130-172), and 5 sets of totals at 
the bottom (rows 173-215).

I want to create a drop-down box that would point to and locate the case 
managers' last names or worker ID#s.  These items are not contiguous--they 
are located at cells B3:B4 for case manager A, I3:I4 for B, P3:P4 for C, etc. 
How can I go about creating a drop-down box for this?
 
Thanks.

0
Reply Utf 3/24/2010 1:22:01 AM

I think what you're saying is you want something so that you can jump to a 
specific block of data peculiar to a specific manager.  If so, you can do 
that several ways.  Yes, you can have a Data Validation cell, choose a 
manager, and a macro will jump the screen to that block.  But an easier way 
is to name the top left cell of each block, say, the manager's name.  You 
can then click on the drop-down arrow in the Name box, displaying all the 
range names, click on the one you want and it happens.  The name box is the 
white space immediately above the "A" of Column A.  Post back if you need 
more or if this is not what you need.  HTH  Otto

"Bradly" <Bradly@discussions.microsoft.com> wrote in message 
news:41740FAC-A9B6-4166-BDE3-BC22FB8AB8C1@microsoft.com...
> I have a large spreadsheet of case manager activity.  Each case manager 
> has a
> certain number of rows and columns for his/her information (case manager A
> uses the area A3:G43, case manager B uses H3:N43, etc.).  There are 13 
> case
> manager areas in group 1 (rows 1-43), 13 in group 2 (rows 44-86), 13 in 
> group
> 3 (rows 87-129), 4 sets in group 4 (rows 130-172), and 5 sets of totals at
> the bottom (rows 173-215).
>
> I want to create a drop-down box that would point to and locate the case
> managers' last names or worker ID#s.  These items are not contiguous--they
> are located at cells B3:B4 for case manager A, I3:I4 for B, P3:P4 for C, 
> etc.
> How can I go about creating a drop-down box for this?
>
> Thanks.
> 
0
Reply Otto 3/24/2010 1:41:19 AM

I tried the name box, but all it says is "print area".  Is there anything 
else I can do?  I'm afraid I don't know all of the ins and outs of control 
boxes and such.


"Otto Moehrbach" wrote:

> I think what you're saying is you want something so that you can jump to a 
> specific block of data peculiar to a specific manager.  If so, you can do 
> that several ways.  Yes, you can have a Data Validation cell, choose a 
> manager, and a macro will jump the screen to that block.  But an easier way 
> is to name the top left cell of each block, say, the manager's name.  You 
> can then click on the drop-down arrow in the Name box, displaying all the 
> range names, click on the one you want and it happens.  The name box is the 
> white space immediately above the "A" of Column A.  Post back if you need 
> more or if this is not what you need.  HTH  Otto
> 
> "Bradly" <Bradly@discussions.microsoft.com> wrote in message 
> news:41740FAC-A9B6-4166-BDE3-BC22FB8AB8C1@microsoft.com...
> > I have a large spreadsheet of case manager activity.  Each case manager 
> > has a
> > certain number of rows and columns for his/her information (case manager A
> > uses the area A3:G43, case manager B uses H3:N43, etc.).  There are 13 
> > case
> > manager areas in group 1 (rows 1-43), 13 in group 2 (rows 44-86), 13 in 
> > group
> > 3 (rows 87-129), 4 sets in group 4 (rows 130-172), and 5 sets of totals at
> > the bottom (rows 173-215).
> >
> > I want to create a drop-down box that would point to and locate the case
> > managers' last names or worker ID#s.  These items are not contiguous--they
> > are located at cells B3:B4 for case manager A, I3:I4 for B, P3:P4 for C, 
> > etc.
> > How can I go about creating a drop-down box for this?
> >
> > Thanks.
> > 
> .
> 
0
Reply Utf 3/24/2010 7:25:01 PM

Bradly
   Apparently, "print area" is the only range name your file has so far. 
You must create the range names you want   To do that, first select the 
cell.  Name that cell.  Look up Range Names in Help if you don't know how to 
name a cell.  Name each such cell.  Now, if you click the down arrow in the 
Name box, you will see all the range names in your file.  Click on the one 
you want and the screen will jump to that cell. Post back if you need more 
help

"Bradly" <Bradly@discussions.microsoft.com> wrote in message 
news:499F624B-F452-4549-AC94-0EE64C721B72@microsoft.com...
> I tried the name box, but all it says is "print area".  Is there anything
> else I can do?  I'm afraid I don't know all of the ins and outs of control
> boxes and such.
>
>
> "Otto Moehrbach" wrote:
>
>> I think what you're saying is you want something so that you can jump to 
>> a
>> specific block of data peculiar to a specific manager.  If so, you can do
>> that several ways.  Yes, you can have a Data Validation cell, choose a
>> manager, and a macro will jump the screen to that block.  But an easier 
>> way
>> is to name the top left cell of each block, say, the manager's name.  You
>> can then click on the drop-down arrow in the Name box, displaying all the
>> range names, click on the one you want and it happens.  The name box is 
>> the
>> white space immediately above the "A" of Column A.  Post back if you need
>> more or if this is not what you need.  HTH  Otto
>>
>> "Bradly" <Bradly@discussions.microsoft.com> wrote in message
>> news:41740FAC-A9B6-4166-BDE3-BC22FB8AB8C1@microsoft.com...
>> > I have a large spreadsheet of case manager activity.  Each case manager
>> > has a
>> > certain number of rows and columns for his/her information (case 
>> > manager A
>> > uses the area A3:G43, case manager B uses H3:N43, etc.).  There are 13
>> > case
>> > manager areas in group 1 (rows 1-43), 13 in group 2 (rows 44-86), 13 in
>> > group
>> > 3 (rows 87-129), 4 sets in group 4 (rows 130-172), and 5 sets of totals 
>> > at
>> > the bottom (rows 173-215).
>> >
>> > I want to create a drop-down box that would point to and locate the 
>> > case
>> > managers' last names or worker ID#s.  These items are not 
>> > contiguous--they
>> > are located at cells B3:B4 for case manager A, I3:I4 for B, P3:P4 for 
>> > C,
>> > etc.
>> > How can I go about creating a drop-down box for this?
>> >
>> > Thanks.
>> >
>> .
>> 
0
Reply Otto 3/26/2010 4:48:04 PM

3 Replies
237 Views

(page loaded in 0.079 seconds)


Reply: