Problem with Circular References

TotallyConfused · May 25, 2017

I work in a garage and I’m creating a program to record our daily sales. The first thing the user does is enter the date in the numeric form of (1 1 2017) which I convert to Excel’s date format, e.g. 42736 (January 1, 2017) and store in a variable called: ROW_EXCEL_DATE_V.
Then they enter data in Column B of the DataEntry sheet as shown below.

[TABLE="class: MsoTableGrid"]
<tbody>[TR]
[TD][/TD]
[TD="width: 120"]

A

[/TD]
[TD="width: 98"]

B

[/TD]
[/TR]
[TR]
[TD="width: 45"]

1

[/TD]
[TD="width: 120"]

OIL

[/TD]
[TD="width: 98"]

35.00

[/TD]
[/TR]
[TR]
[TD="width: 45"]

2

[/TD]
[TD="width: 120"]

PARTS

[/TD]
[TD="width: 98"]

120.00

[/TD]
[/TR]
</tbody>[/TABLE]
Fig. 1: DataEntry sheet
This data (column B) is automatically transferred and stored in another sheet, ‘DataEntryTotalsDB’, which resembles the sample shown below. I coded the number 42736 in ‘A3’. All following numbers in ‘A’ are created by A3+1, A4+1, etc.

[TABLE="class: MsoTableGrid, align: left"]
<tbody>[TR]
[TD]

A

[/TD]
[TD="width: 87"]

B

[/TD]
[TD="width: 91"]

C

[/TD]
[/TR]
[TR]
[TD="width: 137"][/TD]
[TD="width: 87"] TODAY[/TD]
[TD="width: 91"] TODAY[/TD]
[/TR]
[TR]
[TD="width: 137"] EXCELDATE[/TD]
[TD="width: 87"]

OIL

[/TD]
[TD="width: 91"]

PARTS

[/TD]
[/TR]
[TR]
[TD="width: 137"] 42736[/TD]
[TD="width: 87"]

35.00

[/TD]
[TD="width: 91"]

120.00

[/TD]
[/TR]
[TR]
[TD="width: 137"] 42737[/TD]
[TD="width: 87"]

0

[/TD]
[TD="width: 91"]

0

[/TD]
[/TR]
</tbody>[/TABLE]

Fig. 2: DataEntryTotalsDB

The formula in ‘B3’ was: =IF($A3=ROW_EXCEL_DATE_V,DataEntry!$B$1,B3) and extends down. The formula in ‘C3’ was similar except it was: =IF($A3=ROW_EXCEL_DATE_V,DataEntry!B$2,C3) That row (row 3) contains all the sales data for that day. Tomorrow’s data will be placed in row 4 since it’s day 42737. Even though it’s now tomorrow, I still need the data that occurred yesterday, and that is the reason for the ‘B3’ and ‘C3’ part of those formulas, however, they are causing error message of ‘Circular References B3’. All the remaining columns have formulas similar to those shown above, so I’m getting that same error message throughout the whole sheet.
Another member suggested I use another cell to reference ‘B3’ and ‘C3’
To do that I created another sheet named DeTotDBHld

[TABLE="class: MsoTableGrid"]
<tbody>[TR]
[TD][/TD]
[TD="width: 81"]

A

[/TD]
[TD="width: 91"]

B

[/TD]
[/TR]
[TR]
[TD="width: 80"] 1[/TD]
[TD="width: 81"]

TODAY

[/TD]
[TD="width: 91"]

TODAY

[/TD]
[/TR]
[TR]
[TD="width: 80"] 2[/TD]
[TD="width: 81"]

OIL

[/TD]
[TD="width: 91"]

PARTS

[/TD]
[/TR]
[TR]
[TD="width: 80"] 3[/TD]
[TD="width: 81"]

35.00

[/TD]
[TD="width: 91"]

120.00

[/TD]
[/TR]
</tbody>[/TABLE]

Fig. 3: DeTotDBHld

The formula in ‘A3’ (above) is =DataEntryTotalsDB!B3 which gives the value of 35.00. You will notice that between Fig. 2 and Fig. 3, that there is a one-column difference between numbers.
[TABLE="class: MsoTableGrid, align: left"]
<tbody>[TR]
[TD][/TD]
[TD="width: 137"]

A

[/TD]
[TD="width: 87"]

B

[/TD]
[TD="width: 91"]

C

[/TD]
[/TR]
[TR]
[TD="width: 39"] 1[/TD]
[TD="width: 137"][/TD]
[TD="width: 87"]

TODAY

[/TD]
[TD="width: 91"]

TODAY

[/TD]
[/TR]
[TR]
[TD="width: 39"] 2[/TD]
[TD="width: 137"]

EXCELDATE

[/TD]
[TD="width: 87"]

OIL

[/TD]
[TD="width: 91"]

PARTS

[/TD]
[/TR]
[TR]
[TD="width: 39"] 3[/TD]
[TD="width: 137"]

42736

[/TD]
[TD="width: 87"]

35.00

[/TD]
[TD="width: 91"]

120.00

[/TD]
[/TR]
[TR]
[TD="width: 39"] 4[/TD]
[TD="width: 137"]

42737

[/TD]
[TD="width: 87"]

0

[/TD]
[TD="width: 91"]

0

[/TD]
[/TR]
</tbody>[/TABLE]

Fig 4. NEW version of DataEntryTotalsDB

Fig. 4: DataEntryTotalsDB (the old Fig. 2) the formula that is in ‘B3’ now is
=IF($A3=ROW_EXCEL_DATE_V,DataEntry!$B$1,DeTotDBHld!A3) As you can see, there is NO reference to cell ‘B3’. Why am I still getting the same error message?

To summarize my needs:
On day, 42736 when $A3 and ROW_EXCEL_DATE_V ARE EQUAL, then the value of B3 = 35 and C3 = 120. On day 42737, when $A3 and ROW_EXCEL_DATE_V are NOT EQUAL, those cells would remain the same (B3 = 35 and C3 = 120). They will never change unless the user specifically enters that date and makes an adjustment.

My question: How can I modify the formula in B3 (Fig. 4: DataEntryTotalsDB), (and other columns) to achieve my needs, yet avoid the ‘Circular Reference’ error message? I suppose I could create a subroutine to transfer data to this sheet, but I hate to do that because this way everything is interactive. There is no chance of the user forgetting to run the subroutine to update.

I want to THANK YOU in advance for any help you may be able to offer.

Jerry Sullivan · May 25, 2017

It sounds like you are using VBA for at least part of this process (converting user date entries to serial dates).

If that's the case, then I'd suggest using VBA to write each user entry appending it to a database of all entry records. By writing the values instead of using formulas to read the values, you can eliminate the problem of circular references. So that approach would consist of 3 parts:

User entry: This could be a worksheet or a userform
Database: Listing a record for each user entry
Summary: Summarize data from the database by date and service. You could use formulas or a PivotTable for this.

This system would provide more detailed information to assist with audit, or checking.

TotallyConfused · May 25, 2017

Hello Jerry

THANK YOU for your response to my question. I have struggled with this Circular Reference error for several days and have tried everything I could think of. I’m new to Excel, and while I think it is a very powerful tool, there is so much to learn. It seems as if before I can master one thing, I have to figure out something else.

In reply to the first part of your comment about how I converted the user date entries into serial dates, I had them enter the date in cells B1:B3. I then fed this data into =DATE which did the conversion for me.

It appears as if I’m going to have to resort to VBA to save the data. I’ve resisted doing so because by using formulas everything is always up to date the moment they enter something. Maybe there is some way to make a VBA subroutine run automatically if perhaps a cell changes value. Another way I’ve thought of, and wondered if there was some way to detect when the user clicked on another sheet tab, to use that to start a macro. This would write all data to the database and the reports on the other sheets would update before the user saw them. I’m not sure if that is possible or not.

Again, I want to thank you for your help and comments. This is a wonderful site. I’ve learned a lot simply by reading and studying the answers other people get for their questions. Everyone is so helpful, and I might add, patient, with new people like myself.

Jerry Sullivan · May 25, 2017

VBA has Event Procedures that trigger automatically upon some user action such as entering a value or activating a sheet. For your application, I'd suggest you just place a button next to the entry cells. When the user has completed their entries they can click this button to run a macro that will add a record to the database on another worksheet. After the data is transferred, the entry form can be cleared so the user can see the data was recorded.

If you use formulas to summarize the data in your database, it should always be up to date.

Just ask if you want some help with the coding of that macro.

TotallyConfused · May 29, 2017

Hello Jerry

I’m sorry for taking so long to get back to you. I have used a button before, so I think that is the way I may have to go with this. I was glad to hear that Excel does have the means to start a routine automatically. That is an interesting idea and something I’ll have to look into in the future. THANK YOU for your help and suggestion.

Problem with Circular References

TotallyConfused

Board Regular

Excel Facts

Jerry Sullivan

MrExcel MVP

TotallyConfused

Board Regular

Jerry Sullivan

MrExcel MVP

TotallyConfused

Board Regular

Similar threads

Forum statistics

Share this page

Problem with Circular References

TotallyConfused

Board Regular

Excel Facts

Jerry Sullivan

MrExcel MVP

TotallyConfused

Board Regular

Jerry Sullivan

MrExcel MVP

TotallyConfused

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock