labels

P

pamela

I have 52 non-contiguous cells that need to be summed and
averaged. I have created a label by selecting the
individual cells and typing a name in the name box.
How can I edit or review to be sure I have all the correct
cells? i.e. If I skip a cell that needs to be included
or if I click on a cell that should not be included

if the label should reference B5, B10, B15, ... but I
click on B4, B9, B15 how can I change B9 to B10?

Is there an easy way to use the name for relative
formulas? i.e. I have Monday_P1, Monday_p2, Tuesday_P1,
Tuesday_P2 then how can I use this to get Monday_P3 which
is the next column or use it to create Monday_p3 which is
the next row. I have 5 columns and 5 different P rows -
one for each week of the year

any suggestions would be appreciated.

pamela
 
P

Paul B

Pamela, for the first part, click in the name box an pick your name, it
should highlight the cells, to edit this range go to insert, name, define
and pick the name from there to change the range, not clear on the second
part
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 2003
** remove news from my email address to reply by email **
 
P

pamela

When I try to edit a label using the insert, name, define
and choose the name I am unable to add to the list. each
label stops at the same week - Sept 13-17. I have redone
them and still the same results. Is there a limit to the
number of individual cells that can be part of a label?

This is the problem I was running into when doing =average
( I could get through July and then everything was an
error in the formula until I took out every cell after row
154). Of course some of the cells are still blank since
the year is not over.
 
D

Dave Peterson

There is a limit on the length of the list of addresses in the
Insert|Name|define dialog box.

One way around it.

Select your cells (once again!)
hit alt-f11 (to get to the VBE)
hit ctrl-g (to see the immediate window)

type this and hit enter:

selection.name = "yournamehere"

(change yournamehere to what you want <bg>.)

====
Another workaround is to create names in steps.

Select a few/lots of cells (but fewer than the limit).
Give that a nice name.
myPart1

Do it again selecting more cells.
myPart2

Then when you're done, insert|name|define

MyAllParts
refers to:
=mypart1,mypart2,mypart3,mypart4

(as many as you need--until you break the length limit again!)

After you do that, hit F5 (edit|goto or even ctrl-g).
type in the name "myallparts" and hit enter.

You should see your cells selected. You can tab through them
counting/verifying.

And if you go back to the VBE and hit ctrl-g once more, you can type this and
hit enter:

?range("myallparts").cells.count

to see how many of the 52 you got.

======
You may want to take a look at =offset() too.

=OFFSET(mypart1,0,1)

would return the value of the cell to the right of mypart1 (a single cell!).
 
Top