I have data , that can not be stored in only one worksheet , so I must divide
this data into smaller parts, but is it somehow possible to define a dynamic
range with offset function , that contains whole data ?

I am asking this because I use this data in some calculations with dsum
functions , and calculations will be slower if I write each formula with
something like that ?
=DSUM(data1,Amount,criteria)+DSUM(data2,Amount,cri teria)
where data1 and data2 are dynamic ranges produced by offset function in two
seperate worksheets.

thanks for replies

Can you use a database, like Access, to manage your data? Then you can have
it all in one data table and run calculations off that continguous set of
data.
--
Brevity is the soul of wit.


"turrucan" wrote:

I have data , that can not be stored in only one worksheet , so I must divide
this data into smaller parts, but is it somehow possible to define a dynamic
range with offset function , that contains whole data ?

I am asking this because I use this data in some calculations with dsum
functions , and calculations will be slower if I write each formula with
something like that ?
=DSUM(data1,Amount,criteria)+DSUM(data2,Amount,cri teria)
where data1 and data2 are dynamic ranges produced by offset function in two
seperate worksheets.

thanks for replies

it is an ultimate solution , but I wonder if there is a simple way of doing
this in excel ?

"Dave F" wrote:

Can you use a database, like Access, to manage your data? Then you can have
it all in one data table and run calculations off that continguous set of
data.
--
Brevity is the soul of wit.


"turrucan" wrote:

I have data , that can not be stored in only one worksheet , so I must divide
this data into smaller parts, but is it somehow possible to define a dynamic
range with offset function , that contains whole data ?

I am asking this because I use this data in some calculations with dsum
functions , and calculations will be slower if I write each formula with
something like that ?
=DSUM(data1,Amount,criteria)+DSUM(data2,Amount,cri teria)
where data1 and data2 are dynamic ranges produced by offset function in two
seperate worksheets.

thanks for replies

are not there anyone willing to answer ? let me rephrase my questions . I
have two named ranges lets say data1 & data2, How they can be forced to act
like one combined data ?

"turrucan" wrote:

it is an ultimate solution , but I wonder if there is a simple way of doing
this in excel ?

"Dave F" wrote:

Can you use a database, like Access, to manage your data? Then you can have
it all in one data table and run calculations off that continguous set of
data.
--
Brevity is the soul of wit.


"turrucan" wrote:

I have data , that can not be stored in only one worksheet , so I must divide
this data into smaller parts, but is it somehow possible to define a dynamic
range with offset function , that contains whole data ?

I am asking this because I use this data in some calculations with dsum
functions , and calculations will be slower if I write each formula with
something like that ?
=DSUM(data1,Amount,criteria)+DSUM(data2,Amount,cri teria)
where data1 and data2 are dynamic ranges produced by offset function in two
seperate worksheets.

thanks for replies

hi,

Do you these two ranges to appear in a dropdown list? If so, make a
dummy column where you will not do anything except for combining these
two ranges.

=concatnate(A1&" "&B1)

copy down

name this range and use it at the dropdown list.


I hope this will work fine with you and wish someone could give you a
better idea.


Thanks

Shail


turrucan wrote:
are not there anyone willing to answer ? let me rephrase my questions . I
have two named ranges lets say data1 & data2, How they can be forced to act
like one combined data ?

"turrucan" wrote:

it is an ultimate solution , but I wonder if there is a simple way of doing
this in excel ?

"Dave F" wrote:

Can you use a database, like Access, to manage your data? Then you can have
it all in one data table and run calculations off that continguous set of
data.
--
Brevity is the soul of wit.


"turrucan" wrote:

I have data , that can not be stored in only one worksheet , so I must divide
this data into smaller parts, but is it somehow possible to define a dynamic
range with offset function , that contains whole data ?

I am asking this because I use this data in some calculations with dsum
functions , and calculations will be slower if I write each formula with
something like that ?
=DSUM(data1,Amount,criteria)+DSUM(data2,Amount,cri teria)
where data1 and data2 are dynamic ranges produced by offset function in two
seperate worksheets.

thanks for replies

Might you be able to have the basic DSUM function located on each sheet in
individual helper cells and then finally, just sum those helper
cells?....just a thought


Vaya con Dios,
Chuck, CABGx3




"turrucan" wrote:

I have data , that can not be stored in only one worksheet , so I must divide
this data into smaller parts, but is it somehow possible to define a dynamic
range with offset function , that contains whole data ?

I am asking this because I use this data in some calculations with dsum
functions , and calculations will be slower if I write each formula with
something like that ?
=DSUM(data1,Amount,criteria)+DSUM(data2,Amount,cri teria)
where data1 and data2 are dynamic ranges produced by offset function in two
seperate worksheets.

thanks for replies

I thought it was interesting that you used both the (presumably) concatenate
function and operator in the same formula:
=concatnate(A1&" "&B1)

I wroter presumably above as it is either misspelled or a language
difference. I presumed the latter. Just out of curiosity, I copied this
formula to a worksheet and made the spelling change. The results were as
they would have been had either been used alone. So, not a problem although
it could be called redundant. Could have used either =CONCATENATE(A1," ",B1)
or =(A1&" "&B1).

I happen to prefer the latter myself. Although I will admit to using
non-necessary spaces so mine would actually look like: =(A1 & " " & B1).
You could call this a style issue or it could be that since I have used other
programming languages, I don't want to get into a habit that could
potentially cause me problems if/when I go back to using programming
languages where a space would be needed.

--
Kevin Vaughn


"shail" wrote:

hi,

Do you these two ranges to appear in a dropdown list? If so, make a
dummy column where you will not do anything except for combining these
two ranges.

=concatnate(A1&" "&B1)

copy down

name this range and use it at the dropdown list.


I hope this will work fine with you and wish someone could give you a
better idea.


Thanks

Shail


turrucan wrote:
are not there anyone willing to answer ? let me rephrase my questions . I
have two named ranges lets say data1 & data2, How they can be forced to act
like one combined data ?

"turrucan" wrote:

it is an ultimate solution , but I wonder if there is a simple way of doing
this in excel ?

"Dave F" wrote:

Can you use a database, like Access, to manage your data? Then you can have
it all in one data table and run calculations off that continguous set of
data.
--
Brevity is the soul of wit.


"turrucan" wrote:

I have data , that can not be stored in only one worksheet , so I must divide
this data into smaller parts, but is it somehow possible to define a dynamic
range with offset function , that contains whole data ?

I am asking this because I use this data in some calculations with dsum
functions , and calculations will be slower if I write each formula with
something like that ?
=DSUM(data1,Amount,criteria)+DSUM(data2,Amount,cri teria)
where data1 and data2 are dynamic ranges produced by offset function in two
seperate worksheets.

thanks for replies

I guess best way is writing formulas like
DSUM(data1,Amount,criteria)+DSUM(data2,Amount,crit eria), beacuse there is no
way of writing this in one formula like DSUM("data1;data2",Amount,criteria)