Dynamic ranges and reporting

[knox5731]

I cannot get my dynamic ranges to work. I've followed several
articles but can't get it to work for the row/column combination I
have going.


A B C D
E F
1 startmo Feb-08
2 Noofmonths 3
3
4 Jan-08 Feb-08 Mar-08 Apr-08 May-08
5 2 4 6
9 11


Desired end result

6 MAR-08 Apr-08 May-08
7 6 9 11


Row 4 is a named range called "allmonths"
I'd like to have formulas in A6:C7 that dynamically pick up what I
have entered for start mo and noofmonths to form a report.
Help? I have this in excel to anyone that can assist as well to help
explain further if the post doesn't go through right.
Thanks!

K

 

[T. Valko]

If your startmo is Feb-08 shouldn't the output be:

A6.....Feb-08.....Mar-08.....Apr-08
A7.........4..............6................9

 

[knox5731]

If your startmo is Feb-08 shouldn't the output be:

A6.....Feb-08.....Mar-08.....Apr-08
A7.........4..............6................9

--
Biff
Microsoft Excel MVP











- Show quoted text -

Yes you are correct....any ideas on how to accomplish that?

 

[T. Valko]

Let's assume "allmonths" is in the range A4:L4

"allmonths" and "startmo" are true Excel dates formatted as mmm-yy. These
should also be the *same day of the month*, for example:

startmo = 2/1/2008 (formatted as mmm-yy) = Feb-08
allmonths needs to be the same: 1/1/2008, 2/1/2008, 3/1/2008, etc (formatted
as mmm-yy)

Then, to get the output:

Formula in A6 copied over to L6 (format as mmm-yy)

=IF($B1="","",IF(COLUMNS($A6:A6)<=$B2,INDEX(allmonths,MATCH($B1,allmonths,0)+COLUMNS($A6:A6)-1),""))

Formula in A7 copied over to L7:

=IF(A6="","",INDEX($A5:$L5,MATCH(A6,allmonths,0)))

--
Biff
Microsoft Excel MVP

If your startmo is Feb-08 shouldn't the output be:

A6.....Feb-08.....Mar-08.....Apr-08
A7.........4..............6................9

--
Biff
Microsoft Excel MVP











- Show quoted text -

Yes you are correct....any ideas on how to accomplish that?

 

[knox5731]

Let's assume "allmonths" is in the range A4:L4

"allmonths" and "startmo" are true Excel dates formatted as mmm-yy. These
should also be the *same day of the month*, for example:

startmo = 2/1/2008 (formatted as mmm-yy) = Feb-08
allmonths needs to be the same: 1/1/2008, 2/1/2008, 3/1/2008, etc (formatted
as mmm-yy)

Then, to get the output:

Formula in A6 copied over to L6 (format as mmm-yy)

=IF($B1="","",IF(COLUMNS($A6:A6)<=$B2,INDEX(allmonths,MATCH($B1,allmonths,0­)+COLUMNS($A6:A6)-1),""))

Formula in A7 copied over to L7:

=IF(A6="","",INDEX($A5:$L5,MATCH(A6,allmonths,0)))

--
Biff
Microsoft Excel MVP






Yes you are correct....any ideas on how to accomplish that?- Hide quoted text -

- Show quoted text -

I tried the formula, but I wanted to use as much dynamic names as
possible, and then it didn't work.

=IF(startmo="","",IF(COLUMNS($A6:A6)<=noofmonths,INDEX(allmonths,MATCH(startmo,allmonths,
0)+COLUMNS($A6:A6)-1),""))

 

[knox5731]

I tried the formula, but I wanted to use as much dynamic names as
possible, and then it didn't work.

=IF(startmo="","",IF(COLUMNS($A6:A6)<=noofmonths,INDEX(allmonths,MATCH(star­tmo,allmonths,
0)+COLUMNS($A6:A6)-1),""))- Hide quoted text -

- Show quoted text -

Nevermind -- I got it to work. I had a typo.
Thanks!

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

I tried the formula, but I wanted to use as much dynamic names as
possible, and then it didn't work.

=IF(startmo="","",IF(COLUMNS($A6:A6)<=noofmonths,INDEX(allmonths,MATCH(star­tmo,allmonths,
0)+COLUMNS($A6:A6)-1),""))- Hide quoted text -

- Show quoted text -

Nevermind -- I got it to work. I had a typo.
Thanks!