Problem with Circular References

[TotallyConfused]

<o>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 day, month, year, (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.

Code:
A B
11 OIL 35
12 PARTS 120
This data (column B) 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.
Code:
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 (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 ‘E3’ and ‘F3’ part of those formulas, however, that is causing my problem. That part of the formula gives me the error message of ‘Circular References E3’. All the remaining columns have formulas similar to those shown above, so I’m getting that same error message throughout the whole sheet.

To summarize my needs:
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.

My question: How can I modify the formula in E3 (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. </o>

 

[steve the fish]

Where does the 35 come from when you say E3 = 35 and the 120 when F3 = 120?

 

[steve the fish]

Ok I think maybe its becoming a bit clearer. Your only option is VBA or using another cell to reference E3. A formula cant reference itself and of course you would lose the 35 if you replaced it with a formula.

 

[TotallyConfused]

Hello Steve<o></o>
<o> </o>
WOW! That was fast. While you wereanswering my question, I was busy trying to re-edit those two tables so thecolumns would line up. When I managed to do that and submit my post, I was thentold I couldn’t edit because it had been more than ten minutes. At that point,I sent a letter to the administrator about my problems. I APPOLOGIZE to you andanyone reading this question for the mess they are in. I had everything linedup in the TEST HERE forum, but could find no way to submit from there. Stilldon’t know how to do that.<o></o>
<o> </o>
To answer your first question, that ‘35’and the ‘120’ came from the DataEntry sheet.<o></o>
<o> </o>
You are right when you say I’d lose the 35if I replaced it with a formula. I do have to keep all the previous day’snumbers for my monthly and YTD reports. I was wondering, if I created an exactduplicate copy of DataEntryTotalsDB on sheet 2,then in that sheet put the formula of =DataEntryTotalsDB!E3in Sheet 2’s E3 cell, and nothing else so that ‘35’ would be there. Then backin DataEntryTotalsDB I’d change the =IF($A3=ROW_EXCEL_DATE_V,DataEntry!$B$11,E3)to =IF($A3=ROW_EXCEL_DATE_V,DataEntry!$B$11,Sheet2!E3). Would that workokay? If I’m reading your reply correctly, I think that is what you are sayingwhen you say, ‘…using another cell to reference E3.’<o></o>
<o> </o>
One reason I hated to go to VBA isthat in another sheet of this workbook there is a report that gives an up tothe minute showing of our monthly and YTD sales. At any time, the user canclick on that sheet and see our exact sales. If I do have to use VBA, is thereany way to make that subroutine active in the background all the time, so thissecond report would always be up to date according to the last figures the userhad entered? Another option I can think of, is there a way to detect when theuser clicked on that report sheet, and I could intercept that, run thesubroutine to update everything, then let the user see the report?<o></o>
<o> </o>
Again, I do want to THANK YOU Stevefor your time and help here. I appreciate it very much. At times like this, Iwonder why I spent all that time and money going to university to get my degreein Computer Programming. I should have become a ditch digger. Life would be somuch simpler. J<o></o>
<o> </o>