Circular references error message

TotallyConfused · May 4, 2017

Hello
Before I say anything, I think my name describes me perfectly. I really am. I work in a garage and I’m creating a program to record our daily sales. The computer I’m using to write this program runs as Vista and I’m using Excel 2010. The computer the program will be used on is Win. 10 with Excel 2007. The first thing the user will do is enter the date in the numeric form of day, month, year, which I convert to the Excel date format, e.g. 42736 (January 1, 2017). This number is stored in a variable called; ROW_EXCEL_DATE_V. Control then passes to the DataEntry sheet, which resembles this sample:

A B

11 OIL 35

12 PARTS 120

The user will enter their data in Column B. This data is automatically transferred and stored in another sheet, ‘DataEntryTotalsDB’, which resembles the sample shown below. The number 42736 (ExcDate) is coded in ‘A3’. All following numbers in ‘A’ are created by A3+1, A4+1, etc. The ‘B C D’ columns mean ‘DAY NUM’, ‘MONTH NUM’, ‘YEAR NUM’, (e.g. =YEAR(A3) which I thought I’d need when I set this up, however, since I’ve never used that data, I think I can delete these three columns. I’ve left them here for your reference in case they play a part in what I hope will be your answer to my problem.

A B C D E F

EXCDATE DNUM MNUM YNUM OIL PARTS

3 42736 1 1 2017 35 120

4 42737 2 1 2017

The formula in ‘E3’ is: =IF($A3=ROW_EXCEL_DATE_V,DataEntry!$B$11,E3) and extends down. The formula in ‘F3’ is similar except it’s: =IF($A3=ROW_EXCEL_DATE_V,DataEntry!B$12,F3) That row 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 ‘E3’ part of that formula, however, that is causing my problem. That part of the formula gives me the error message of ‘Circular References E3’. It was only through a LOT of trial and error that I figured out that was causing the problem.

My question to you is how do I record the day’s sales and/or save the previous sales in any given cell? I’m fairly new to Excel so I’m sure there are a great many commands I don’t know about. I have tried various combinations of ‘IF’ and ‘VLOOKUP’ commands, but nothing works. The moment I enter ‘E3’, I’m in trouble. 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.

The range DataEntryTotalsDbRn apply to cells A3:F4. The monthly report has the formula for total OIL sales of; =DSUM(DataEntryTotalsDbRn,5,CalcFormulas!B129:C130). Would this somehow be causing part of the problem? The code of ‘CalcFormulas!B129:C130’ refers to the criteria used in the DSUM formula and looks like this:

A B C

129 DNUM MNUM YNUM
130 1 1 2017

On a side note, and I don’t know if this would have any bearing on my problem or not, but it might. When I look at the way Excel is set up, by going through FILE – OPTIONS – FORMULAS I notice the Workbook is set to ‘Automatic calculate’. The option of ‘Enable iterative calculation’ IS checked. The ‘Maximum Iterations’ are set to 100 and the ‘Maximum Change’ is set to 0.001. I’m not sure what all this means, so I don’t know if this is a good setting or not. If not, would you be able to suggest how I should change them to be most efficient?

I will appreciate any help you may be able to offer. THANK YOU!

JoeMo · May 4, 2017

A circular reference error occurs when the formula in a cell uses the cell itself as a precedent. I haven't read carefully your lengthy post, but in a few words, can you tell us what you want the value of cell E3 (and F3) to be if $A3 is not equal to ROW_EXCEL_DATE_V?

TotallyConfused · May 5, 2017

JoeMo said:
A circular reference error occurs when the formula in a cell uses the cell itself as a precedent. I haven't read carefully your lengthy post, but in a few words, can you tell us what you want the value of cell E3 (and F3) to be if $A3 is not equal to ROW_EXCEL_DATE_V?

Hello JoeMo

I have read many questions from users where you people from MrExcel had to write back and ask for more information. I didn’t want that to happen here, but I guess I got a bit carried away and I apologize for that.

To answer your question, cells E3 (and F3) contain our sales for that day as shown in the DataEntry screen above. On day, 42736 when $A3 and ROW_EXCEL_DATE_V ARE EQUAL, then the value of E3 = 35 and F3 = 120. On day 42737, when $A3 and ROW_EXCEL_DATE_V are NOT EQUAL, those cells would remain the same (E3 = 35 and F3 = 120). They will never change unless the user specifically enters that date and makes an adjustment.

I’m hoping this can be done with Excel formulas, and not have to resort to VBA, however, if that is not the case, then it can’t be helped. THANK YOU for your prompt response.

Circular references error message

TotallyConfused

Board Regular

Excel Facts

JoeMo

MrExcel MVP

TotallyConfused

Board Regular

Similar threads

Forum statistics

Share this page

Circular references error message

TotallyConfused

Board Regular

Excel Facts

JoeMo

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