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]

Did you try every step from that long list?

Yes I did, and it failed, so traced it back with dummy data and it was the =MATCH(9.99999999999999E+307,Expenses!$B:$B) that didn't work. So I'm wondering if =OFFSET(Expenses!$B$2,0,0,COUNTA(Expenses!$B:$B),1) would effectively be a substitute for 'Lrec'?

 

[Aladin Akyurek]

Yes I did, and it failed, so traced it back with dummy data and it was the =MATCH(9.99999999999999E+307,Expenses!$B:$B) that didn't work. So I'm wondering if =OFFSET(Expenses!$B$2,0,0,COUNTA(Expenses!$B:$B),1) would effectively be a substitute for 'Lrec'?

Curious why...

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

would not work. Is the sheet name correct?

Also, What is the system you are working on?

 

[kcmuppet]

Yes the sheet name is definitely correct. I'm wondering if it requires no spaces or just numbers in the column, whereas my data is as described in post 6

 

[Aladin Akyurek]

Yes the sheet name is definitely correct. I'm wondering if it requires no spaces or just numbers in the column, whereas my data is as described in post 6

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

will ignore all non-numeric values (including errors) and pick out the last numeric value from the reference. If no numeric value exists, it will return #N/A. Why don't you test this claim on a different sheet on some reference, say, Sheet1, column A?

 

[kcmuppet]

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

will ignore all non-numeric values (including errors) and pick out the last numeric value from the reference. If no numeric value exists, it will return #N/A. Why don't you test this claim on a different sheet on some reference, say, Sheet1, column A?

I've no doubt about the 'claim', I'm just trying to understand why it's not working for me.

 

[kcmuppet]

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.

=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)

T. Valko, does Excel treat this as a range or is it looking for just the lower and upper dates?:

 

[T. Valko]

T. Valko, does Excel treat this as a range or is it looking for just the lower and upper dates?:

It is a range.

If you give that formula the defined name of MyRange then you can use it in other functions like:

=SUM(MyRange)
=MAX(MyRange)
=COUNTIF(MyRange,"x")
etc
etc

 

[kcmuppet]

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

will ignore all non-numeric values (including errors) and pick out the last numeric value from the reference. If no numeric value exists, it will return #N/A. Why don't you test this claim on a different sheet on some reference, say, Sheet1, column A?

Aladin, I found out that your list of DNRs will allow me to produce a pivot table reference without an error message, if I use =MATCH(9.99999999999999E+307,Expenses!$B$1:$B$15000) for Lrec

Lrec =produces 1431, which is correct.

However, the Data reference pulls all the dates in Expenses!$B$1:$B$15000 into the pivot, not just between the Lbound & Ubound

 

[Aladin Akyurek]

Aladin, I found out that your list of DNRs will allow me to produce a pivot table reference without an error message, if I use =MATCH(9.99999999999999E+307,Expenses!$B$1:$B$15000) for Lrec

Lrec =produces 1431, which is correct.

However, the Data reference pulls all the dates in Expenses!$B$1:$B$15000 into the pivot, not just between the Lbound & Ubound

Aladin, I found out that your list of DNRs will allow me to produce a pivot table reference without an error message, if I use =MATCH(9.99999999999999E+307,Expenses!$B$1:$B$15000) for Lrec

Lrec =produces 1431, which is correct.

However, the Data reference pulls all the dates in Expenses!$B$1:$B$15000 into the pivot, not just between the Lbound & Ubound

I misinterpreted your need... So, let's rethink the issue.

1. We have a data area in B:R on Expenses.

2. We have headers in B1:R1. Note that the headers/fields are required for running a pivot table.

3. We have two dates (date-1 and date-2) on Sheet1 which must be used to define a subarea within B:R,
running from date-1 to date-2. Not a big deal. However, the subarea will not (cannot) include
the headers/fields if date-1 is not on the second row, so it cannot be fed to the pivot table functionality.

If doing the required processing with formulas is an option, we can create a dynamic
named range for the subarea date-1 and date-2 specify.

 

[kcmuppet]

1. We have a data area in B:R on Expenses.

Yes

2. We have headers in B1:R1. Note that the headers/fields are required for running a pivot table.

In B2:R2 actually.

3. We have two dates (date-1 and date-2) on Sheet1 which must be used to define a subarea within B:R, running from date-1 to date-2.

Yes. Or rather it could be a sub area if the rows are sorted by col B. Also, in case it makes a difference, there are often more than one row with the same date, and some dates for which there are no rows.

However, the subarea will not (cannot) include
the headers/fields if date-1 is not on the second row

Date one (the earlier date) definitely won't be in the second row.

If doing the required processing with formulas is an option, we can create a dynamic named range for the subarea date-1 and date-2 specify.

It is