Circular Error that I do not understand

[ziggle]

Hi folks,

I have been given a workbook to maintain that has monthly financial data in it. Each sheet is named after the month and year ("Jan 2011", "Feb 2011", etc). I will have 12 sheets by the end of the year. I need to maintain a year-to-date (TYD) sum on each page. I would like to automate our process and allow a worksheet to compute the previous month's sheet name and automatically reference the previous month's data when doing the YTD calculation. However, when I do this I get a circular reference error.

I can create a simple example. Assume my first worksheet is named "Jan 2011." I have some data in cell A1. I copy this sheet and name the copy "Feb 2011." The new worksheet has a function in it that computes the previous month's name using CELL("filename") and some string commands. I then use the INDIRECT command to reference data on the "Jan 2011" worksheet. It is this INDIRECT command that generates the circular reference error.

I do not see where I am screwing up.

Ziggle

 

[GlennUK]

Show your formula.

 

[ziggle]

The formula to determine the previous sheet name is:

=TEXT(DATE(YEAR(DATEVALUE(RIGHT(CELL("filename"),8))),MONTH(DATEVALUE(RIGHT(CELL("filename"),8)))-1,1),"MMM YYYY")

The formula to access a cell on that sheet is:

=INDIRECT("'"&RIGHT(CELL("filename"),8)&"'!"&"A2")

Ziggle

 

[GlennUK]

No, this formula:

=INDIRECT("'"&RIGHT(CELL("filename"),8)&"'!"&"A2")

that you say is to access a cell on the previous sheet is NOT pointing at the previous sheet at all.

You have a formula to determine the previous sheet name, which I'm sure is giving you the right answer .... use that within the INDIRECT function.

 

[ziggle]

You are correct -- but my problem is still there. My supposedly simplified example was not what I was really doing. Here is a better description.

Assuming I am working with this month's totals (March 2011). I first compute the name of the previous month's report, which is "Feb 2011". I then use INDIRECT to access the year-to-date total on that sheet ("'Feb 2011'!A2"). It is here that I get the circular reference error. Here are my formulas.

Compute name of previous month in "MMM YYYY" format (stored in cell B1).

=TEXT(DATE(YEAR(DATEVALUE(RIGHT(CELL("filename"),8))),MONTH(DATEVALUE(RIGHT(CELL("filename"),8)))-1,1),"MMM YYYY")

Access YTD cell on previous sheet (this is in B2).

=INDIRECT("'"&B1&"'!"&"A2").

My status bar shows "Circular:B2" and I get a circular error message.

Ziggle

 

[GlennUK]

And what are the cell contents of the previous month cell A2? Trace the dependency back, and see where each precedent cell precedents lead, to make sure that nothing loops back on itself.

 

[jasonb75]

Do you have a cell anywhere on your sheet that shows ANY date in the current month?

If yes, does this cure the problem?

In B1 =TEXT(EOMONTH(F1,-1),"MMM YYYY"))

Where F1 is a cell holding a current month date.

 

[jasonb75]

Having read through the thread again, my last suggestion will not make any difference, as Glenn said in #6, you need to trace the dependancy back to the source. There is nothing in any of the formula that you have posted that would cause the circular reference warning.

However, you could still shorten the formula in B1 to my previous suggestion, or to =TEXT((1&" "&RIGHT(CELL("filename"),8))-1,"MMM YYYY") which would still serve the same purpose.

 

[ziggle]

Hi folks,

I have tried to trace it down and I can't find it. I have been able to make it go away, however. This shouldn't have worked, but I wrote a VB routine that reads the current sheet name, which is of the form "MMM YYYY". Just as my formula did, the VB routine generates a reference to the sheet with the name one month earlier. This has worked great! Why ... I have no idea. This sure was frustrating.

Anyway, I appreciate all your efforts. Thanks!

Ziggle