Does anyone have a way to have non-contiguous criteria for a DSUM function? I
have a worksheet with a range of data that I need to run many similar queries
on but with one single changing criteria item. so I'd like to have a row of
field headings at the top and just copy down the criteria variables below and
with the actual DSUM function for each query in each copied row. Something
like this:
Item StartDate EndDate Location Qty
Item1 =38517 <=39067 Loc1 =DSUM(StockList,"Qty",A11:D12)
item2 =38517 <=39067 Loc1 =DSUM(StockList,"Qty",(A11:D11,A13:D13))

I know this formula doesn't work but does anyone have a way to do this?
The problem is, I have lots of rows and these query results are used in
formulas on another sheet and if I have to include the column heading above
each criteria, it means hand linking each cell.
Thanks in advance,
RDW

Try using a data table with an extra column and row which you can hide.
Assuming the data in the example is in the range A11:E13...

a) Insert a column in column E and enter an index next to the data:

E
12 | 1
13 | 2
14 | 3 ...

b) Insert a line below the titles and enter in A12:E12 (from the
right):

12 | =OFFSET(A12,E12,) =OFFSET(B12,E12,) =OFFSET(C12,E12,)
=OFFSET(D12,E12,) 1

c) Select E12:F15 and choose Data Table Column input cell E12:

E F
12 | 1 =DSUM(stocklist,"Qty",A11:D12)
13 | 1 =TABLE(,E12)
14 | 2 =TABLE(,E12)
15 | 3 =TABLE(,E12)


RD Wirr wrote:

Does anyone have a way to have non-contiguous criteria for a DSUM function? I
have a worksheet with a range of data that I need to run many similar queries
on but with one single changing criteria item. so I'd like to have a row of
field headings at the top and just copy down the criteria variables below and
with the actual DSUM function for each query in each copied row. Something
like this:
Item StartDate EndDate Location Qty
Item1 =38517 <=39067 Loc1 =DSUM(StockList,"Qty",A11:D12)
item2 =38517 <=39067 Loc1 =DSUM(StockList,"Qty",(A11:D11,A13:D13))

I know this formula doesn't work but does anyone have a way to do this?
The problem is, I have lots of rows and these query results are used in
formulas on another sheet and if I have to include the column heading above
each criteria, it means hand linking each cell.
Thanks in advance,
RDW

Hi Lori,
Thanks very much for your help. This looks interesting but I am still having
a little trouble with it. I have setup a test sheet with the following data
and formulas:
A B C D E
F
Date Item Location Qty Index
=OFFSET(A12,E12,) =OFFSET(B12,E12,) =OFFSET(C12,E12,) =OFFSET(D12,E12,) 1 =DSUM(Stocklist,"Qty",A11:D12)
11/23/06 Item1 Loc1 3 2 {=Table(,E12)}
11/23/06 Item1 Loc2 10 3 {=Table(,E12)}
11/23/06 Item2 Loc3 25 4 {=Table(,E12)}

The Stocklist range is A11:E15. I have created the table as you said but
this creates a circular reference. I must have something overlapping but I
can't find it.

The other thing is, My data is on one sheet and then on my separate query
sheet I have around 700 rows with 14 queries on each row, each extracting a
different bit of information about a particular "Item" from the datasheet. A
typical single query looks like this:
Item Date Date Location Query
Item1 ="=38517" ="<=39067" Loc1 =DSUM(Stocklist,"Qty",A11:D12)

The datasheet has 1000's of rows of item information as you see it at the
top. These rows of data are expanded daily and referenced by a dynamic range.
So my question is, assuming I can get this offset criteria to work, How do I
reference the query data on my separate query page?

Thanks again for your help,
RDW

"RD Wirr" wrote:

Does anyone have a way to have non-contiguous criteria for a DSUM function? I
have a worksheet with a range of data that I need to run many similar queries
on but with one single changing criteria item. so I'd like to have a row of
field headings at the top and just copy down the criteria variables below and
with the actual DSUM function for each query in each copied row. Something
like this:
Item StartDate EndDate Location Qty
Item1 =38517 <=39067 Loc1 =DSUM(StockList,"Qty",A11:D12)
item2 =38517 <=39067 Loc1 =DSUM(StockList,"Qty",(A11:D11,A13:D13))

I know this formula doesn't work but does anyone have a way to do this?
The problem is, I have lots of rows and these query results are used in
formulas on another sheet and if I have to include the column heading above
each criteria, it means hand linking each cell.
Thanks in advance,
RDW

Hi - It looks like you may be adding the data table to the stocklist
table instead of the criteria table.
Try this simple example below (it may need a little reorganising after
pasting):

Stocklist (named range):

Date Item Location Qty
2006-11-23 Item1 Loc1 3
2006-11-23 Item1 Loc2 10
2006-11-23 Item2 Loc3 25

Criteria (A11:E14):

Item Date Date Location Index Qty
=OFFSET(A12,E12,) =OFFSET(B12,E12,) =OFFSET(C12,E12,) =OFFSET(D12,E12,)
1 =DSUM(Stocklist,"Qty",A11:D12)
Item1 =2005-06-14 <=2006-12-16 Loc1 1 =TABLE(,E12)
item2 =2005-06-15 <=2006-12-17 Loc3 2 =TABLE(,E12)



RD Wirr wrote:

Hi Lori,
Thanks very much for your help. This looks interesting but I am still having
a little trouble with it. I have setup a test sheet with the following data
and formulas:
A B C D E
F
Date Item Location Qty Index
=OFFSET(A12,E12,) =OFFSET(B12,E12,) =OFFSET(C12,E12,) =OFFSET(D12,E12,) 1 =DSUM(Stocklist,"Qty",A11:D12)
11/23/06 Item1 Loc1 3 2 {=Table(,E12)}
11/23/06 Item1 Loc2 10 3 {=Table(,E12)}
11/23/06 Item2 Loc3 25 4 {=Table(,E12)}

The Stocklist range is A11:E15. I have created the table as you said but
this creates a circular reference. I must have something overlapping but I
can't find it.

The other thing is, My data is on one sheet and then on my separate query
sheet I have around 700 rows with 14 queries on each row, each extracting a
different bit of information about a particular "Item" from the datasheet. A
typical single query looks like this:
Item Date Date Location Query
Item1 ="=38517" ="<=39067" Loc1 =DSUM(Stocklist,"Qty",A11:D12)

The datasheet has 1000's of rows of item information as you see it at the
top. These rows of data are expanded daily and referenced by a dynamic range.
So my question is, assuming I can get this offset criteria to work, How do I
reference the query data on my separate query page?

Thanks again for your help,
RDW

"RD Wirr" wrote:

Does anyone have a way to have non-contiguous criteria for a DSUM function? I
have a worksheet with a range of data that I need to run many similar queries
on but with one single changing criteria item. so I'd like to have a row of
field headings at the top and just copy down the criteria variables below and
with the actual DSUM function for each query in each copied row. Something
like this:
Item StartDate EndDate Location Qty
Item1 =38517 <=39067 Loc1 =DSUM(StockList,"Qty",A11:D12)
item2 =38517 <=39067 Loc1 =DSUM(StockList,"Qty",(A11:D11,A13:D13))

I know this formula doesn't work but does anyone have a way to do this?
The problem is, I have lots of rows and these query results are used in
formulas on another sheet and if I have to include the column heading above
each criteria, it means hand linking each cell.
Thanks in advance,
RDW

Cool! That works like a charm. Much faster than the 1000's of lookups I had
to use on my earlier solution. Thanks Lori for the clever setup.

BTW, I notice that the of "data table" for the index is a little fussy about
the order in which you set it up. I had to build this test setup several
times to get the sequence right without creating circular references when I
created the data table. Each time I ended up abandoning the old worksheet
because the circular references couldn't seem to be cleared. Anyway. It works
now.

Thanks again.
RDW

"Lori" wrote:

Hi - It looks like you may be adding the data table to the stocklist
table instead of the criteria table.
Try this simple example below (it may need a little reorganising after
pasting):

Stocklist (named range):

Date Item Location Qty
2006-11-23 Item1 Loc1 3
2006-11-23 Item1 Loc2 10
2006-11-23 Item2 Loc3 25

Criteria (A11:E14):

Item Date Date Location Index Qty
=OFFSET(A12,E12,) =OFFSET(B12,E12,) =OFFSET(C12,E12,) =OFFSET(D12,E12,)
1 =DSUM(Stocklist,"Qty",A11:D12)
Item1 =2005-06-14 <=2006-12-16 Loc1 1 =TABLE(,E12)
item2 =2005-06-15 <=2006-12-17 Loc3 2 =TABLE(,E12)



RD Wirr wrote:

Hi Lori,
Thanks very much for your help. This looks interesting but I am still having
a little trouble with it. I have setup a test sheet with the following data
and formulas:
A B C D E
F
Date Item Location Qty Index
=OFFSET(A12,E12,) =OFFSET(B12,E12,) =OFFSET(C12,E12,) =OFFSET(D12,E12,) 1 =DSUM(Stocklist,"Qty",A11:D12)
11/23/06 Item1 Loc1 3 2 {=Table(,E12)}
11/23/06 Item1 Loc2 10 3 {=Table(,E12)}
11/23/06 Item2 Loc3 25 4 {=Table(,E12)}

The Stocklist range is A11:E15. I have created the table as you said but
this creates a circular reference. I must have something overlapping but I
can't find it.

The other thing is, My data is on one sheet and then on my separate query
sheet I have around 700 rows with 14 queries on each row, each extracting a
different bit of information about a particular "Item" from the datasheet. A
typical single query looks like this:
Item Date Date Location Query
Item1 ="=38517" ="<=39067" Loc1 =DSUM(Stocklist,"Qty",A11:D12)

The datasheet has 1000's of rows of item information as you see it at the
top. These rows of data are expanded daily and referenced by a dynamic range.
So my question is, assuming I can get this offset criteria to work, How do I
reference the query data on my separate query page?

Thanks again for your help,
RDW

"RD Wirr" wrote:

Does anyone have a way to have non-contiguous criteria for a DSUM function? I
have a worksheet with a range of data that I need to run many similar queries
on but with one single changing criteria item. so I'd like to have a row of
field headings at the top and just copy down the criteria variables below and
with the actual DSUM function for each query in each copied row. Something
like this:
Item StartDate EndDate Location Qty
Item1 =38517 <=39067 Loc1 =DSUM(StockList,"Qty",A11:D12)
item2 =38517 <=39067 Loc1 =DSUM(StockList,"Qty",(A11:D11,A13:D13))

I know this formula doesn't work but does anyone have a way to do this?
The problem is, I have lots of rows and these query results are used in
formulas on another sheet and if I have to include the column heading above
each criteria, it means hand linking each cell.
Thanks in advance,
RDW

Thanks for the comeback - glad you got it working.

RD Wirr wrote:

Cool! That works like a charm. Much faster than the 1000's of lookups I had
to use on my earlier solution. Thanks Lori for the clever setup.

BTW, I notice that the of "data table" for the index is a little fussy about
the order in which you set it up. I had to build this test setup several
times to get the sequence right without creating circular references when I
created the data table. Each time I ended up abandoning the old worksheet
because the circular references couldn't seem to be cleared. Anyway. It works
now.

Thanks again.
RDW

"Lori" wrote:

Hi - It looks like you may be adding the data table to the stocklist
table instead of the criteria table.
Try this simple example below (it may need a little reorganising after
pasting):

Stocklist (named range):

Date Item Location Qty
2006-11-23 Item1 Loc1 3
2006-11-23 Item1 Loc2 10
2006-11-23 Item2 Loc3 25

Criteria (A11:E14):

Item Date Date Location Index Qty
=OFFSET(A12,E12,) =OFFSET(B12,E12,) =OFFSET(C12,E12,) =OFFSET(D12,E12,)
1 =DSUM(Stocklist,"Qty",A11:D12)
Item1 =2005-06-14 <=2006-12-16 Loc1 1 =TABLE(,E12)
item2 =2005-06-15 <=2006-12-17 Loc3 2 =TABLE(,E12)



RD Wirr wrote:

Hi Lori,
Thanks very much for your help. This looks interesting but I am still having
a little trouble with it. I have setup a test sheet with the following data
and formulas:
A B C D E
F
Date Item Location Qty Index
=OFFSET(A12,E12,) =OFFSET(B12,E12,) =OFFSET(C12,E12,) =OFFSET(D12,E12,) 1 =DSUM(Stocklist,"Qty",A11:D12)
11/23/06 Item1 Loc1 3 2 {=Table(,E12)}
11/23/06 Item1 Loc2 10 3 {=Table(,E12)}
11/23/06 Item2 Loc3 25 4 {=Table(,E12)}

The Stocklist range is A11:E15. I have created the table as you said but
this creates a circular reference. I must have something overlapping but I
can't find it.

The other thing is, My data is on one sheet and then on my separate query
sheet I have around 700 rows with 14 queries on each row, each extracting a
different bit of information about a particular "Item" from the datasheet. A
typical single query looks like this:
Item Date Date Location Query
Item1 ="=38517" ="<=39067" Loc1 =DSUM(Stocklist,"Qty",A11:D12)

The datasheet has 1000's of rows of item information as you see it at the
top. These rows of data are expanded daily and referenced by a dynamic range.
So my question is, assuming I can get this offset criteria to work, How do I
reference the query data on my separate query page?

Thanks again for your help,
RDW

"RD Wirr" wrote:

Does anyone have a way to have non-contiguous criteria for a DSUM function? I
have a worksheet with a range of data that I need to run many similar queries
on but with one single changing criteria item. so I'd like to have a row of
field headings at the top and just copy down the criteria variables below and
with the actual DSUM function for each query in each copied row. Something
like this:
Item StartDate EndDate Location Qty
Item1 =38517 <=39067 Loc1 =DSUM(StockList,"Qty",A11:D12)
item2 =38517 <=39067 Loc1 =DSUM(StockList,"Qty",(A11:D11,A13:D13))

I know this formula doesn't work but does anyone have a way to do this?
The problem is, I have lots of rows and these query results are used in
formulas on another sheet and if I have to include the column heading above
each criteria, it means hand linking each cell.
Thanks in advance,
RDW