Circular Error that I do not understand

ziggle

New Member
Joined
May 9, 2007
Messages
16
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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

ziggle

New Member
Joined
May 9, 2007
Messages
16
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
 
Upvote 0

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,723
No, this formula:
Code:
=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.
 
Upvote 0

ziggle

New Member
Joined
May 9, 2007
Messages
16
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
 
Upvote 0

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,723
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.
 
Upvote 0

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
15,488
Office Version
  1. 365
Platform
  1. Windows
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.
 
Upvote 0

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
15,488
Office Version
  1. 365
Platform
  1. Windows
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.
 
Upvote 0

ziggle

New Member
Joined
May 9, 2007
Messages
16
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
 
Upvote 0

Forum statistics

Threads
1,191,554
Messages
5,987,245
Members
440,086
Latest member
Mahi786

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top