Return Range Based on Cell Value

[Pablo]

This topic has been covered in varying degrees, but the problem is
that I do not want to sum or count the range. I want the range itself
returned as the value.

For instance in A1 is 7. i.e. July
In B1-B12 are dates 201101, 201102, etc.

I need a formula that will count out the cells and return the range
based on that value in A1. In this case it would return B1:B7. This of
course would be nested in another formula.

 

[Ron Rosenfeld]

This topic has been covered in varying degrees, but the problem is
that I do not want to sum or count the range. I want the range itself
returned as the value.

For instance in A1 is 7. i.e. July
In B1-B12 are dates 201101, 201102, etc.

I need a formula that will count out the cells and return the range
based on that value in A1. In this case it would return B1:B7. This of
course would be nested in another formula.

=OFFSET(B1,0,0,A1)

If your date strings are sequential, then the above formula would return:

={201101;201102;201103;201104;201105;201106;201107}

 

[Pablo]

Thanks. That isn't quite what I needed because it returns all the
dates, but it got me to what I did.

I used this in the formula: =$B$1:OFFSET($A$1,0,$A$1,1)

so the offset returned B7 and the end result was B1:B7.

 

[Ron Rosenfeld]

Thanks. That isn't quite what I needed because it returns all the
dates, but it got me to what I did.

I used this in the formula: =$B$1:OFFSET($A$1,0,$A$1,1)

so the offset returned B7 and the end result was B1:B7.

The formula I gave returns the dates in B1:B7 which is what you wrote you wanted (you wrote dates B1:b12)

Your formula returns the contents of B1:H1. If that is what you want, you would be better off with:

=OFFSET(B1,0,0,1,$A$1)

 

[Pablo]

Yeah, B1:H1. That's what I meant. Thanks for the help.