Inserting new rows receiving circular reference

**jeffreybrown** · 03-08-2011, 11:31 AM

This bug is alluding me so hope I can get some help.

I'm setting up a method to insert rows across all spreadsheets in the workbook. I've trimmed the workbook down to the input sheet and just one other sheet for testing.

On Sheets("POD") the user will click on a cell in column A right above where they would like to add new rows.

So, if the user wants to add 2 rows right below Process Number 2 they would double click on A9 and a user form will appear asking for either Insert or Delete and the numbers of rows.

This is all working fine and the premise of the macro comes from Insert a Row using a Macro to maintain formulas; however, if the rows are inserted anywhere above row 14 that's when the circular reference appears.

I double click on Sheets("POD").Range("A9") and select Insert >> 2 rows. The insert takes place, but the circular reference comes up pointing to Sheets("5").Range("P16").

When I point to error checking it shows up as in column K. I can see one thing which looks weird, the formula in column P jumps over those two new rows so instead on the formula in P9 referencing P10 it referenced P12.

Not sure how to fix this...any ideas?

**jeffreybrown** · 03-09-2011, 07:37 AM

Any thoughts?

**Greg M** · 03-09-2011, 08:00 AM

Hi there,

I haven't yet got around to figuring out WHY this solution doesn't produce a Circular Reference error message, but maybe you'll be satisfied with the fact that it DOESN'T seem to produce one.

In general I try to avoid references which involve performing calculations on entire rows or on entire columns - that's why I decided to edit one of your formulas to see what would happen.

Each formula in Column P of the "5" worksheet contains several references to "C:C" and "P:P". In Cell P8 I changed each of these references to "C8:C999" and "P8:P999", and then copied the formula downwards as far as Cell P19. (Row 8 is the first row which contains data. You can increase/decrease the "999" to suit your data requirements)

I then returned to the "POD" worksheet, double-clicked on Cell A9, selected "Insert" and "2" on the UserForm, and then clicked on "OK".

Result: two rows added on each of the two worksheets, and no Circular Reference error message.

I feel that Excel is doing something "strange" with the original workbook - it seems peculiar that the Circular Reference is always generated in Cell P16 regardless of which row (located above Row 14) in the "POD" worksheet is selected as the reference row for new rows to be inserted.

Anyway, enough of my ramblings, I hope the above is of some help to you - PLEASE let me know how you get on, I'd love to know if my proposed solution works at your end.

Regards,

Greg M

**jeffreybrown** · 03-09-2011, 08:45 AM

Thanks Greg for the feedback. At first I did have the rollup total in a different column, but because I am charting these values I went with the same column. Of course when I made that change and then tried to make the formula dynamic to find the last row this is causing the circular reference.

For now this is going to have to work until I can find another workable solution. Of course what concerns me with this method is somebody deletes blank rows below the data. If I have C8:C999 and the data only extends to C100 and they delete C101:C999 not knowing it is necessary to stay then this could cause problems.

Again, thanks for the feedback as I believe this will work for now.

**Greg M** · 03-09-2011, 09:55 AM

Hi Jeff,

Ok on your worry about a user deleting blank rows before Row 999. This won't be a problem if you use Named Range Formulas as follows:

On the "5" worksheet define three Names:

Name = ptrMaxTasks, Value = 999
Name = ptrLastTask, Value = =INDIRECT("C" & '5'!ptrMaxTasks, 1)
Name = ptrLastPAT, Value = =INDIRECT("P" & '5'!ptrMaxTasks, 1)

Now, in Cell P8, change each instance of "C8:C999" to "C8:ptrLastTask", and each instance of "P8:P999" to "P8:ptrLastPAT".

Copy the above formulas downwards to Cell P19.

Now, you can delete as many blank rows as you like - ptrLastTask will ALWAYS point to Cell C999, and ptrLastPAT to Cell P999. The trick is to use the INDIRECT formula to define the addresses of these cells.

I've made the above changes to the attached copy of your original workbook.

I hope this helps, but as before, please let me know how you get on with this.

Regards,

Greg M