Indirect range reference at top of worksheet

[p|q]

Hello,

I am trying to reference a range of cells using indirect references. I need to use the same equation for the entire column. I want the range to shift with the cell it is entered in (which works), but with my current equation the range extends above the first cell in the worksheet it flips to the bottom.

Equation used:
=SUM(INDIRECT("R[-5]C[-1]:RC[-1]",0))

When the equation is placed in B3, the interpreted range is $A$1:$A$1048573, when I would like it to be $A$1:$A$2.

Any suggestions for modifications or alternate approaches would be greatly appreciated.

[FDibbins]

INDIRECT() is used to convert text into something that excel can use in a formula . Perhaps you need to look at OFFSET()? what exactly are you trying to do?

[p|q]

Thanks for the reply.

I think I might be able to use OFFSET the same effect but it still results in an error at the top of the worksheet.
Per Microsoft: If rows and cols offset reference over the edge of the worksheet, OFFSET returns the #REF! error value.

I am trying to create a single equation using relative cell references that will shift a range with it as it moves down the worksheet.

[Jonmo1]

I don't understand what you're trying to achieve...

Can you post a few examples of what you want the formula to look like using standard A1 style.

You said

When the equation is placed in B3, the interpreted range is $A$1:$A$1048573, when I would like it to be $A$1:$A$2.

Add a few more examples
In B4, I want it to be xxx
In B10, I want it to be xxx
etc..

[p|q]

I have attached an example spreadsheet that shows the result using INDIRECT and OFFSET equations as well as the result I am trying to produce. Thanks.

IndirectRangeReference.xlsx

[FDibbins]

whats wrong with just plain ol =SUM($A$2:A2)

[Jonmo1]

Try this in B2 and filled down

=SUM(OFFSET(A2,0,0,-MIN(5,ROWS($1:1)),1))

[Jonmo1]

whats wrong with just plain ol =SUM($A$2:A2)

The range should be limited to 5 cells..
In B6 it's =SUM(A2:A6)
but in B7 it's =SUM(A3:A7)

[FDibbins]

oops missed that part, thanks Jonmo1

[p|q]

There are two issues with that approach (for my use). I am trying to create the equation for a database that will be created automatically with the same equation in each row of the column so I need to use relative cell references. I can create a functional equation that always starts the range at the top, but with 100,000+ rows of data its not practical.

[Jonmo1]

Can you make it so the data starts in Row 5?
Then it's just =SUM(A1:A5) and filled down.

[p|q]

Try this in B2 and filled down

=SUM(OFFSET(A2,0,0,-MIN(5,ROWS($1:1)),1))

That did the trick, Thanks!

I modified the equation so it only uses indirect references:
=SUM(OFFSET(INDIRECT("RC[-2]",0),0,0,-MIN(5,ROWS($1:1)),1))

[Jonmo1]

Here's a version that looks exactly the same no matter which cell you put it in.

=SUM(OFFSET($A$2,ROW()-2,0,-MIN(5,ROW()-1),1))

[p|q]

Perfect! That's actually better. Thanks!

[Jonmo1]

Glad to help.

FYI, it's prone to give erroneous results if a row is inserted/deleted above row2

The other one is actually 'better', but didn't quite satisfy your needs.