Long Block & "Name Box"

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Hi everyone,

Say I have a block of raws and columns that want to name in a
worksheet using "Box Name".

However, the number of raws is large, or so large, in terms of 100Ks
raws; I don't know what is the max # of raws in Excel new versions.

Yet, I want to use "Box Name" so I use the block name in my work
later. What is the best/smart way to handle this?

Thanks,
Mike
 
If the range is a single area (like A1:X9999), then the number of rows won't
matter.

If you're trying to use Insert|name|Define (or its equivalent in xl2007), then
there's a limit on the number of characters in the address (256 I _think_ in
xl2003).

But if you're trying to name a bunch of non-contiguous rows (and over 100k
rows/cells), then using a name may not be the best technique.

You may want to explain what you're doing a little more.
 
If the range is a single area (like A1:X9999), then the number of rows won't
matter.

If you're trying to use Insert|name|Define (or its equivalent in xl2007),then
there's a limit on the number of characters in the address (256 I _think_in
xl2003).

But if you're trying to name a bunch of non-contiguous rows (and over 100k
rows/cells), then using a name may not be the best technique.

You may want to explain what you're doing a little more.
Click to expand...

Dave,

The block is just few columns, about 6 columns, but the number of raws
might range from 10Ks to 100Ks sometimes!? Yes, it is a continuous
single block or single area. So if I have 150K raws, then the block
will be 6 columns by 150K raws which I want to name using Box Name as
"COMBOS"

Usually, I would highlight the block and, in the Box Name, I would
type COMBOS and hit return. Does it mean I should highligh the 6
columns and scrol-down all the way to the last raw allowed, which is
by the way 65536, and then name the block? If so, does it mean if it
turned that I have 100K combos, i.e. 100K raws, it will still be
there?

Thanks,
Mike
 
Say I have a block of raws and columns that want to name in a
The block is just few columns, about 6 columns, but the number of raws
might range from 10Ks to 100Ks sometimes!? Yes, it is a continuous
single block or single area. So if I have 150K raws, then the block
will be 6 columns by 150K raws which I want to name using Box Name as
"COMBOS"

Usually, I would highlight the block and, in the Box Name, I would
type COMBOS and hit return. Does it mean I should highligh the 6
columns and scrol-down all the way to the last raw allowed, which is
by the way 65536, and then name the block? If so, does it mean if it
turned that I have 100K combos, i.e. 100K raws, it will still be
there?
Click to expand...

Just type your address range in the Name Box first in order to select the
range, then type the name you want to give it in the Name Box to name it.
For example, using your initial posts range of A1:X9999.... just type
A1:X9999 in the Name Box and hit the enter key... that range will then be
selected. Now you can Name the range just as if you had manually selected
the cell yourself.
 
Just type your address range in the Name Box first in order to select the
range, then type the name you want to give it in the Name Box to name it.
For example, using your initial posts range of A1:X9999.... just type
A1:X9999 in the Name Box and hit the enter key... that range will then be
selected. Now you can Name the range just as if you had manually selected
the cell yourself.
Click to expand...

Could you please clarify how this will be written in the Name Box? Is
it like this "a2:x9999=COMBOS" for example? Walk through
please....Thanks
 
Go to the name box:
type: A1:X9999
and hit enter.

This selected that large range.

Now while that range is still selected, go back to the name box and type:
COMBOS
and hit enter.

This gives that selected range a name (if that name is not in use already).

ps.

Selecting a range that has tons of rows sometimes means that you're padding the
range with empty cells--just in case their filled in.

If that's the case, you could create a name that grew and contracted based on
the data.

Debra Dalgleish shares instructions for that kind of dynamic name:
http://contextures.com/xlNames01.html#Dynamic
 
Go to the name box:
type:  A1:X9999
and hit enter.

This selected that large range.

Now while that range is still selected, go back to the name box and type:
COMBOS
and hit enter.

This gives that selected range a name (if that name is not in use already).

ps.

Selecting a range that has tons of rows sometimes means that you're padding the
range with empty cells--just in case their filled in.

If that's the case, you could create a name that grew and contracted based on
the data.

Debra Dalgleish shares instructions for that kind of dynamic name:http://contextures.com/xlNames01.html#Dynamic







--

Dave Peterson- Hide quoted text -

- Show quoted text -
Click to expand...

The number of COMBOS is not known in advance to pre-define. I tried
a2:f9999 as said above and then named it "COMBOS, it worked!

BUT, f9999 is NOT enough! When I tried a2:f99999 ( i.e. 5 9s) it
didn't take it! Well, I want to name a range of a2:f999999 (yes 6
9s).....How this is done? Any smart way?

I hope there is a way?

Mike
 
BUT, f9999 is NOT enough! When I tried a2:f99999 ( i.e. 5 9s) it
didn't take it! Well, I want to name a range of a2:f999999 (yes 6
9s).....How this is done? Any smart way?
Click to expand...

Go back to Dave's post and read his "PS" section and then go to the link
that he posted in it.
 
First, if you've tried the dynamic range stuff and are happy with that, then
this is just background...

But I'm confused.

You say you have 150k rows. But then you write that the last row is 65536.

So if you really, really wanted and you're using xl97-xl2003, you're limited to
that 64k rows. You'd type:

A1:X65536

If you're using xl2007 and your last row is still 65536, then you're running in
compatibility mode. Save your file as a normal workbook, close it and reopen
that new copy.

Now it should be a "normal" xl2007 workbook with 1 million rows per worksheet.

And you could type:
A1:x99999 (or even larger) in the name box.
 
Back
Top