dynamic named range based on range of dates

[kcmuppet]

Hi,

I would like use a dynamic named range as a pivot table source with but pull only rows within a date range, specified in two cells outside the table

The full range is:
=OFFSET(Expenses!$B$2,0,0,COUNTA(Expenses!$B:$B),16)

...where col B contains dates

I'm thinking it probably needs to be using INDEX & MATCH, but I keep getting stuck on how to specify a range between two dates, instead of just matching a specific date.

Could somebody point me in the direction please?

Thanks very much.

 

[kcmuppet]

Here's as far as I've got:

=Expenses!$B$2:INDEX(Expenses!$B$2:$R$10000,MATCH(TRUE,INDEX(AND(Expenses!$B$2:$B$10000<=sheet1!B7,Expenses!$B$2:$B$10000>=sheet1!b8)),FALSE)),0)

What appreciate some help understanding where I'm going wrong. Is it the use of the AND function?

Thanks

 

[Aladin Akyurek]

Here's as far as I've got:

=Expenses!$B$2:INDEX(Expenses!$B$2:$R$10000,MATCH(TRUE,INDEX(AND(Expenses!$B$2:$B$10000<=sheet1!B7,Expenses!$B$2:$B$10000>=sheet1!b8)),FALSE)),0)

What appreciate some help understanding where I'm going wrong. Is it the use of the AND function?

Thanks

Define Lrec as referring to:

=MATCH(9.99999999999999E+307,Expenses!$B:$B)

Define DateRange as referring to:

=Expenses!$B$2:INDEX(Expenses!$B:$B,Lrec)

Define LBound as referring to:

=Sheet1!$B$7

Define UBound as referring to:

=Sheet1!$B$8

Define CondLrec as referring to:

=MATCH(1,IF(DateRange>=LBound,IF(DateRange<=Ubound,1)),0)

Define DATA as referring to:

=Expenses!$B$2:INDEX(Expenses!$R:$R,CondLrec)

 

[kcmuppet]

Hi Aladin,

Thanks for your response.

It's an ingenious looking solution (not sure I understand it all), but unfortunately

=MATCH(9.99999999999999E+307,Expenses!$B:$B) produces "Reference not Valid" for me.

Is the idea that this will find the last cell date, in the range?

Would an offset be a better way to do this bit?

 

[T. Valko]

Here's as far as I've got:

=Expenses!$B$2:INDEX(Expenses!$B$2:$R$10000,MATCH(TRUE,INDEX(AND(Expenses!$B$2:$B$10000<=sheet1!B7,Expenses!$B$2:$B$10000>=sheet1!b8)),FALSE)),0)

What appreciate some help understanding where I'm going wrong. Is it the use of the AND function?

Thanks

Try this...

Sheet2 A1 = lower date boundary
Sheet2 B1 = upper date boundary

=INDEX(Expenses!$B:$Q,MATCH(Sheet2!$A$1,Expenses!$B:$B,0),0):INDEX(Expenses!$B:$Q,MATCH(Sheet2!$B$1,Expenses!$B:$B,0),0)

 

[kcmuppet]

Try this...

Sheet2 A1 = lower date boundary
Sheet2 B1 = upper date boundary

=INDEX(Expenses!$B:$Q,MATCH(Sheet2!$A$1,Expenses!$B:$B,0),0):INDEX(Expenses!$B:$Q,MATCH(Sheet2!$B$1,Expenses!$B:$B,0),0)

Hi T,

That produces reference not valid for me. Do the dates have to be in order for that to work?

Perhaps I should explain a bit more about the data in Expenses!$B:$B? The table header is in Row 2, then a couple of thousand rows of dates, then variable gap of 20-30 rows, then a line of text

 

[T. Valko]

Hi T,

That produces reference not valid for me. Do the dates have to be in order for that to work?

Perhaps I should explain a bit more about the data in Expenses!$B:$B? The table header is in Row 2, then a couple of thousand rows of dates, then variable gap of 20-30 rows, then a line of text

No, the dates do not have to be sorted.

It works for me as a general use dynamic range but I didn't try to use it as the source for a pivot table.

Sorry, I don't have any other suggestions.

 

[Aladin Akyurek]

Hi Aladin,

Thanks for your response.

It's an ingenious looking solution (not sure I understand it all), but unfortunately

=MATCH(9.99999999999999E+307,Expenses!$B:$B) produces "Reference not Valid" for me.

Is the idea that this will find the last cell date, in the range?

Would an offset be a better way to do this bit?

Yes, that formula is supposed to yield the row number of the last date value in column B on Expenses.

 

[kcmuppet]

Yes, that formula is supposed to yield the row number of the last date value in column B on Expenses.

OK, so would =OFFSET(Expenses!$B$2,0,0,COUNTA(Expenses!$B:$B),1) do the same?

 

[Aladin Akyurek]

OK, so would =OFFSET(Expenses!$B$2,0,0,COUNTA(Expenses!$B:$B),1) do the same?

Did you try every step from that long list?