Google Sheets Formulas

Post Reply
smckinney
Newbie
Posts: 9
Joined: Wed Oct 28, 2020 3:36 pm

Google Sheets Formulas

Post by smckinney »

I have a few rather complex formulas I would like to use with google sheets connector app but I can't quite seem to get the <<rowNumber>> to work in this particular formula. I have used <<rowNumber>> in other more simple formulas but there's something about this one.
  • Expense Code::=INDEX('MMFulfillment Billing [Switch.Date:Format="yyyy-MM-dd",TimeZone="UTC"]'!$F$2:$F$9000,MATCH(0,COUNTIF($A$1:A1,'MMFulfillment Billing [Switch.Date:Format="yyyy-MM-dd",TimeZone="UTC"]'!$F$2:$F$9000), 0))::formula
  • Shipping::=SUMIF('MMFulfillment Billing [Switch.Date:Format="yyyy-MM-dd",TimeZone="UTC"]'!$F:$F,$A<<rowNumber>>,'MMFulfillment Billing [Switch.Date:Format="yyyy-MM-dd",TimeZone="UTC"]'!$C:$C)::formula
In the first example we are looking for unique values through a list. I would like to replace the 1 with rowNumber so that it is only counted if the contents from such sheet in column F are not included in A1 through A<<rowNumber>>. Note this formula as i have provided currently works. I just need to reference the current row number and not specifically cell A1.

In the 2nd example we are getting the sum from said sheet in column c where A<rowNumber> matches column F in said sheet. Again wanting to reference the current contents in cell A<rowNumber> to search the other sheet and not a specifc cell
Any insight as to why this isn't working or how this might work would be appreciated. 
User avatar
ezFlow
Newbie
Posts: 9
Joined: Mon Jun 18, 2018 6:51 pm

Re: Google Sheets Formulas

Post by ezFlow »

This was solved using a pivot table:
Google Sheets Connect feeds a data table.
New rows are added throughout the day, and the pivot table in another sheet will evolve all day.
At night, both sheets are exported automatically to Excel or XML files, and the data sheet is reset, ready for next day.

Adding a date column and using filters in the pivot table is also an option.
No need to reset the data table/sheet then.
Post Reply