Range Spanning worksheets

[Guest]

I posted this a while ago but got no response but I'd like to try again.

I have a workbook that has a large (long) data table on one sheet. The table
is referenced as a dynamic range. I access this data via DSUM and some other
database functions. Some time in the next month we will reach the
maximum number of rows filled on the data sheet. We use the history on
this datasheet in our reports so we don't want to consolidate it. So the
question is, is there any way to have the dynamic range span multiple
worksheets within a workbook? Or, is there a way to have the DSUM function
e.g. =DSUM(inventory,"qty",O20:R21), access and operate on more than one
range, like a range continuing on a different sheet?
Thanks in advance,
RDW

 

[Guest]

A range encompasses cells on a worksheet. you can try to create a range
spanning worksheets with something like:

Sub try_spanning()
Dim r As Range
Dim r1 As Range, r2 As Range
Set r1 = Worksheets("Sheet1").Range("A1")
Set r2 = Worksheets("Sheet2").Range("A1")
MsgBox (r1.Address)
MsgBox (r2.Address)
Set r = Union(r1, r2)
End Sub

but this will generate a 1004 error on the union. Try generating a
collection of ranges with logic to move from range to range as appropriate.

 

[Guest]

1. considering that the number of columns (256) is not critical
2. the number of rows of record is not enough, need to extend in another
sheet.

based on my few test of Dsum, only

a. on mainsheet where your formula Dsum is located along with the criteria
on the top rows.

b. on 2nd,3rd.... sheets where your inventory range can be located as
extension.
where row 1 must contains the same text column title
then define another name like "inventory2" for 2nd sheet
range..."inventory3" for 3rd sheet range....

formula for DSUM on the mainsheet.
=dsum(inventory,"qty",O20:R21) + dsum(inventory2,"qty",O20:R21)
this will extend the inventory range by just classically summing the sheets
you need.

 

[Guest]

Hi Gary's Student,

Thanks for the response. But my problem is I have to evaluate the entire
list in the same equation. Using the suggestion of Driller (following
responder), it works for some of my calculations but fails for some things
like the dynamic range I use as a list for a Data Validation drop down list
to look up specific row records.

Any other ideas?

Rgds,
RDW

 

[Guest]

Hi Driller,

Thanks for the suggestion. Why didn't I think of that? It works for simple
DSUM. I have a couple of Data Validation Dropdown lists that get their data
from dynamic ranges, referencing the sheets of data described. I have tried
adding the range names from both sheets together in the "refers to" field on
the define range box to create a third spanning range by combining the two
sheet ranges. It works for spanning ranges for a value list in a drop down
list.

I tried using that same method for the database in a DSUM but no dice. Seem
like it has to create some kind of huge array calculation in the background
that takes forever (judging from the result in the formula auditing).

Anyway, looks like this will work. I still have to work out a spanning
DAVERAGE but I guess this will be something like the product of the daverages
of the two sheets.

Thanks,
RDW

 

[Guest]

you're welcome

practice and experimenting is the best tool you can have for free..
happy holidays.