-1

I am trying to add value of cell A1 to cell A2. However, I want to keep adding A1's value to A2's current value because I want to keep some monthly record and that is represented by A2. I will keep changing A1's value daily.

One way I found is the use of circular reference. But, the problem is, wherever I type something, It also gets added in the A2, which is weird and not required. How do I do this without any VBA because I want to use the formula in Google Spreadsheet.

I have also attached a demo GIF image. It shows that circular reference is not working well. Demo GIF

Awais Saeed
  • 3
  • 2
  • 2
    So develop it in Googlesheets not in Excel. – Solar Mike Jul 04 '23 at 07:31
  • I verified in Google sheets too but the behavior is same there too. – Awais Saeed Jul 04 '23 at 07:42
  • 1
    Maybe, but some functions don't exist in both, while some that do have different layouts... – Solar Mike Jul 04 '23 at 07:43
  • 1
    How does Excel (or GS) know you've added a new number? What if tomorrow you want to add the same number? What you're asking doesn't really fit within a normal spreadsheet without some programming/event control. – CLR Jul 04 '23 at 07:56
  • Yes that is a legit scenario. I was unable to find solutions on the internet and that is why I posted here. Plus, programming would not do the trick because it is not possible in Google spreadsheets. – Awais Saeed Jul 04 '23 at 08:46
  • "But, the problem is, wherever I type something, It also gets added in the A2, which is weird and not required." This happens because iteration overrides "Circular reference" protections and instead puts the workbook into a "recalculation pending" mode (shown by the "Recalculate" message in the status bar"). The pending recalculation takes place whenever a change is made to the workbook - including by changing or updating any cell other than `A1`. Not what you want, but not weird - just Excel's standard behaviour when Circular reference protection is switched off. – DMM Jul 04 '23 at 11:20

1 Answers1

1

Your tags and question itself are misleading. You use Excel tags, show GIF of Excel, but want solution for Google Sheets. Note that Excel and Google Sheets may share most of the formulas, but these are different apps and you often need different solutions for various tasks.

Anyway there is no solution for your task using simple formulas. In Excel you would need to use VBA, in Google Sheets - Apps Script. Since you want it in Google Sheets there are some ways you could do this. One way is to write a script using onEdit(e) trigger so calculation is made when certain cell is altered or you could add button which does calculation you want. Simple solution for latter:

Add button and assign script to it:

function myFunction() {
  var toadd = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange("A1").getValue();
  var tosum = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange("A2").getValue()
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange("A2").setValue(tosum+toadd);
}

Any time you press the button it reads A1, A2 values and sets A2 value to A1+A2

Result:

enter image description here

user11222393
  • 3,245
  • 3
  • 13
  • 23
  • Not a criticism of this answer, but a full solution would also need an ability to (a) determine whether `A1` had actually been added to the cumulative total in `A2` (b) correct for an incorrect daily value in `A1` having been added to `A2` and (c) identify the day of the last value added to the cumulative total (to deal with issue of multiple days having the same value). If the data has any importance, the OP would do well to consider having an explicit monthly list of daily values rather than the outlined `A1`/`A2` approach. – DMM Jul 04 '23 at 11:35
  • 1
    I didn't know about Apps Script and that we can automate Google spreadsheets with it. I googled the beginner's tutorials which made me able to use it and understand your snippet. I also added a button as you did. Now, @DMM is right that it is not a full solution but it is an easier approach compared to what I have been doing in the past that is opening up the calculator every time, do the calculations, and type those answers in the cells. – Awais Saeed Jul 06 '23 at 06:17
  • Yes, this is not full solution it is only a demonstration that it is possible to do it in Google Sheets with Apps Script. Feel free to post another question on SO if you are stuck on developing full script. – user11222393 Jul 06 '23 at 06:39