vba - Google Sheets Addon or Formula that accomplishes the same thing as the Prevsheet Macro's for Excel -

i want have cumulative total running on each sheet (so current total value on current sheet summed cumulative total previous sheet, each sheet representing day of month. in excel definable in macro similar this.

function prevsheet(rg range)  n = application.caller.parent.index  if n = 1  prevsheet = cverr(xlerrref)  elseif typename(sheets(n - 1)) = "chart"  prevsheet = cverr(xlerrna)  else  prevsheet = sheets(n - 1).range(rg.address).value  end if  end function 

is there anyway in google sheets. keeps referring sheet 1.

i left editable link here copy of working on.

https://docs.google.com/spreadsheets/d/1cx9w9vojxkj2wqybojouz7dwqp1vunfypzbmljdpkkq/edit?usp=sharing

you can using script. try this:

function prevsheet(range) {   var ss = spreadsheetapp.getactivespreadsheet();   var sheets = ss.getsheets();   var active = ss.getactivesheet().getname();   (i in sheets) {     if (sheets[i].getname() === active) {       return (sheets[i-1].getrange(range).getvalues());     }   } } 

the problem is, have push range string surrounding quotation marks.

this give range of data

=prevsheet("d2:d6") 

this give sum

=sum(prevsheet("d2:d6")) 

---

update:

if want updated, use script

function prevsheetname() {   var ss = spreadsheetapp.getactivespreadsheet();   var sheets = ss.getsheets();   var active = ss.getactivesheet().getname();   (i in sheets) {     if (sheets[i].getname() === active) {       return (sheets[i-1].getname());     }   } } 

and values this:

=indirect(prevsheetname()&"!d2") 

and

=sum(indirect(prevsheetname()&"!d2:d6")) 

hope helps.