Data contains more than 65000 rows ?

[Guest]

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,criteria)
where data1 and data2 are dynamic ranges produced by offset function in two
seperate worksheets.

thanks for replies

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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 ?

 

[shail]

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

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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