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!

Can you explain the logic behind that code? It's one thing to post the
code but its another to explain it...


Bob Phillips wrote:
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!

INT is explained in help as is MOD.

--
HTH

Bob Phillips

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

wrote in message
oups.com...
Can you explain the logic behind that code? It's one thing to post the
code but its another to explain it...


Bob Phillips wrote:
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!