Another Dynamic OFFSET & INDIRECT query - Circular Ref error

[ififthelement]

I have data starting from Row 9 of a Col and I am using Offset to determine the number of rows used from Row 9 so that I can sum up everything

Earlier I was using which generated Circular Ref error
=SUM(N$9:OFFSET(N$9,(ROW()-10),0))

This is the formula I am using now, also generated Circular Ref error
=SUM(INDIRECT("N$9"):OFFSET(INDIRECT("N$9"),(ROW()-10),0))


How to solve this issue ?

 

[DebugGalpin]

What cell are you putting the formula in?

 

[ififthelement]

ofcourse, the Column N ! the intent is to sum up the data in col N, The rows in Col N are dynamic based on the number of modules executed that day. The formula is inserted at the bottom of Col-N using VBA. Application.recalculate works. till this point everything works perfect.

but whenever you do SAVE it shows Circular-Ref error dialog box. is there any way to avoid this circular ref error ?

 

[ukmikeb]

Hi

If you have no data prior to N9 and your data is contiguous, then :-

=SUM(OFFSET($N$9,,,COUNT($N:$N),1))

hth

 

[ififthelement]

that didnt solve

 

[ukmikeb]

that didnt solve

That is not exactly a helpful response as you have omitted clarification on the error you are now getting.

You could have been more helpful in you response to @DebugGalpin by specifyng an exact cell that the formula was placed in as a result of your latest run.

However provided that the formula is placed in the cell immediately following your data, this formula :-

=SUM(OFFSET($N$9,,,ROW()-9))

should do the trick.

 

[ififthelement]

Hi Mike,

Thanks for the reply. I understood the issue. The Circular Ref error will next get resolved if I keep the formula in the Excel Sheet. Instead, Now, I am inserting the formula via VBA. So anytime i do CTRL+S or RECALCULATE . there is no chance of cyclic error.

@All: thanks for your time guys. This issue was so confusing and frustrating for me. There are hell lot of formulae in the workbook & never faced such error. This literally took my breath away when I was Saved & closed the Wkb.