Hey all,

I am trying to setup of a worksheet that has ranges A:F as a named
ranged using the name "DB1_#" (# = Cells 1 - 65535), I have noticed
that when I try to create a mass quantity of named ranges Excel craps
out on me at around 65407...anyone have any idea as to why this is or
if there is a macro I could run to create a worksheet with 65535 named
ranges using that setup? See example below if your confused...

ROW 1: DB1_1 (Ranges A:F)
ROW 2: DB1_2 (Ranges A:F)
ROW 3: DB1_3 (Ranges A:F)
ROW 4: DB1_4 (Ranges A:F)

|||||||||||||||||||||||||||||

ROW 65536: DB1_65536 (Ranges A:F)

Thank you kindly!

Seems an awful lot of names. Why do you need so many, there must be other
ways?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
oups.com...
Hey all,

I am trying to setup of a worksheet that has ranges A:F as a named
ranged using the name "DB1_#" (# = Cells 1 - 65535), I have noticed
that when I try to create a mass quantity of named ranges Excel craps
out on me at around 65407...anyone have any idea as to why this is or
if there is a macro I could run to create a worksheet with 65535 named
ranges using that setup? See example below if your confused...

ROW 1: DB1_1 (Ranges A:F)
ROW 2: DB1_2 (Ranges A:F)
ROW 3: DB1_3 (Ranges A:F)
ROW 4: DB1_4 (Ranges A:F)

|||||||||||||||||||||||||||||

ROW 65536: DB1_65536 (Ranges A:F)

Thank you kindly!

I am designing an application with a submit function...program will
also allow for add, edit, and delete information inside of a database
(which is why its being titled DB1_# (# = Cell number)...I imagine by
the time I get done with this there will be like 300,000 named ranges
inside this single workbook alone...I know this sounds crazy but I am
trying to figure out the best way to allow for data extraction and
modification...would be much easier with named ranges...


Bob Phillips wrote:
Seems an awful lot of names. Why do you need so many, there must be other
ways?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
oups.com...
Hey all,

I am trying to setup of a worksheet that has ranges A:F as a named
ranged using the name "DB1_#" (# = Cells 1 - 65535), I have noticed
that when I try to create a mass quantity of named ranges Excel craps
out on me at around 65407...anyone have any idea as to why this is or
if there is a macro I could run to create a worksheet with 65535 named
ranges using that setup? See example below if your confused...

ROW 1: DB1_1 (Ranges A:F)
ROW 2: DB1_2 (Ranges A:F)
ROW 3: DB1_3 (Ranges A:F)
ROW 4: DB1_4 (Ranges A:F)

|||||||||||||||||||||||||||||

ROW 65536: DB1_65536 (Ranges A:F)

Thank you kindly!

I agree on the concept, I use the same approach myself, but I never needed
300,000!

Are you saying you have 300,000 fields, or is there some other reason for
300,000 (I just can't get my head around 300,000 fields)?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
oups.com...
I am designing an application with a submit function...program will
also allow for add, edit, and delete information inside of a database
(which is why its being titled DB1_# (# = Cell number)...I imagine by
the time I get done with this there will be like 300,000 named ranges
inside this single workbook alone...I know this sounds crazy but I am
trying to figure out the best way to allow for data extraction and
modification...would be much easier with named ranges...


Bob Phillips wrote:
Seems an awful lot of names. Why do you need so many, there must be
other
ways?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
oups.com...
Hey all,

I am trying to setup of a worksheet that has ranges A:F as a named
ranged using the name "DB1_#" (# = Cells 1 - 65535), I have noticed
that when I try to create a mass quantity of named ranges Excel craps
out on me at around 65407...anyone have any idea as to why this is or
if there is a macro I could run to create a worksheet with 65535 named
ranges using that setup? See example below if your confused...

ROW 1: DB1_1 (Ranges A:F)
ROW 2: DB1_2 (Ranges A:F)
ROW 3: DB1_3 (Ranges A:F)
ROW 4: DB1_4 (Ranges A:F)

|||||||||||||||||||||||||||||

ROW 65536: DB1_65536 (Ranges A:F)

Thank you kindly!

Bob,

The information I have been asked to form a database with search
functions would require a database of 4 - 5 diffrent worksheets (each
containing 65,000 rows). Due to the VLookup and Hlookup structure this
makes it very difficult to form a database that would make more logical
sense instead of having 5 diffrent pieces of information in 4 - 5
diffrent locations. I thought about using the ActiveCell.Value function
with "Range(ActiveCell.Value).Select" to goto the areas however since I
am not going to be the one to use the program I find that it would be
more stable with 300,000 names (each having a diffrent location to pin
point)...example

DB1_100 - Database 1 Row 100
DB2_225 - Database 2 Row 225
DB3_34445 - Database 3 Row 34,445
DB4_33211 - Database 4 Row 33,211

The database it's self has a internal refrence number (standard 1 -
infinity number that I created) to refrence the information so that
Vlookup will only need to search for a number instead of a string of
numbers (thought this would make it easier to have a delete / edit
function inside the program). I guess everyone's programs inside of
Excel is designed diffrently...I stay away from VB code as much as
possiable but use it quite often...I like to stick to the built-in
functions of Excel versus using custom script for my programs, both get
the job done. If you know of a better way of doing this then by all
means let me know because I have my computer running a macro creating
these names one at a time but it takes like 5 sec each one...5 sec each
name = roughly 30 hours for 1 database with names to be created
successfully...



Bob Phillips wrote:
I agree on the concept, I use the same approach myself, but I never needed
300,000!

Are you saying you have 300,000 fields, or is there some other reason for
300,000 (I just can't get my head around 300,000 fields)?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
oups.com...
I am designing an application with a submit function...program will
also allow for add, edit, and delete information inside of a database
(which is why its being titled DB1_# (# = Cell number)...I imagine by
the time I get done with this there will be like 300,000 named ranges
inside this single workbook alone...I know this sounds crazy but I am
trying to figure out the best way to allow for data extraction and
modification...would be much easier with named ranges...


Bob Phillips wrote:
Seems an awful lot of names. Why do you need so many, there must be
other
ways?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
oups.com...
Hey all,

I am trying to setup of a worksheet that has ranges A:F as a named
ranged using the name "DB1_#" (# = Cells 1 - 65535), I have noticed
that when I try to create a mass quantity of named ranges Excel craps
out on me at around 65407...anyone have any idea as to why this is or
if there is a macro I could run to create a worksheet with 65535 named
ranges using that setup? See example below if your confused...

ROW 1: DB1_1 (Ranges A:F)
ROW 2: DB1_2 (Ranges A:F)
ROW 3: DB1_3 (Ranges A:F)
ROW 4: DB1_4 (Ranges A:F)

|||||||||||||||||||||||||||||

ROW 65536: DB1_65536 (Ranges A:F)

Thank you kindly!

Could you not just a lookup function that tests the lookup value and then
retrieves from the appropriate page. As you have very ordered index of 1 to
infinity, you can easily determine which page it is on. You don't even need
VLLOKUP, assuming that the sheets are called Data1, Data2, etc, you could
use

="'Data"&INT(F4/65536)+1&"'!A"&MOD(F4-1,65536)+1


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
oups.com...
Bob,

The information I have been asked to form a database with search
functions would require a database of 4 - 5 diffrent worksheets (each
containing 65,000 rows). Due to the VLookup and Hlookup structure this
makes it very difficult to form a database that would make more logical
sense instead of having 5 diffrent pieces of information in 4 - 5
diffrent locations. I thought about using the ActiveCell.Value function
with "Range(ActiveCell.Value).Select" to goto the areas however since I
am not going to be the one to use the program I find that it would be
more stable with 300,000 names (each having a diffrent location to pin
point)...example

DB1_100 - Database 1 Row 100
DB2_225 - Database 2 Row 225
DB3_34445 - Database 3 Row 34,445
DB4_33211 - Database 4 Row 33,211

The database it's self has a internal refrence number (standard 1 -
infinity number that I created) to refrence the information so that
Vlookup will only need to search for a number instead of a string of
numbers (thought this would make it easier to have a delete / edit
function inside the program). I guess everyone's programs inside of
Excel is designed diffrently...I stay away from VB code as much as
possiable but use it quite often...I like to stick to the built-in
functions of Excel versus using custom script for my programs, both get
the job done. If you know of a better way of doing this then by all
means let me know because I have my computer running a macro creating
these names one at a time but it takes like 5 sec each one...5 sec each
name = roughly 30 hours for 1 database with names to be created
successfully...



Bob Phillips wrote:
I agree on the concept, I use the same approach myself, but I never
needed
300,000!

Are you saying you have 300,000 fields, or is there some other reason
for
300,000 (I just can't get my head around 300,000 fields)?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
oups.com...
I am designing an application with a submit function...program will
also allow for add, edit, and delete information inside of a database
(which is why its being titled DB1_# (# = Cell number)...I imagine by
the time I get done with this there will be like 300,000 named ranges
inside this single workbook alone...I know this sounds crazy but I am
trying to figure out the best way to allow for data extraction and
modification...would be much easier with named ranges...


Bob Phillips wrote:
Seems an awful lot of names. Why do you need so many, there must be
other
ways?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
oups.com...
Hey all,

I am trying to setup of a worksheet that has ranges A:F as a named
ranged using the name "DB1_#" (# = Cells 1 - 65535), I have
noticed
that when I try to create a mass quantity of named ranges Excel
craps
out on me at around 65407...anyone have any idea as to why this is
or
if there is a macro I could run to create a worksheet with 65535
named
ranges using that setup? See example below if your confused...

ROW 1: DB1_1 (Ranges A:F)
ROW 2: DB1_2 (Ranges A:F)
ROW 3: DB1_3 (Ranges A:F)
ROW 4: DB1_4 (Ranges A:F)

|||||||||||||||||||||||||||||

ROW 65536: DB1_65536 (Ranges A:F)

Thank you kindly!