sillycar_wp
Forum Replies Created
-
I forgot to post the link where Karl_S at stack overflow helped me with the first part of this solution.
So, I did find a couple of solutions (In case it’s helpful to anyone else).
The first was to not send the formulas as hidden variables, but to use ARRAYFORMULAS in the top row of a column.(Thanks to Karl_S at stack overflow for helping me!)
https://stackoverflow.com/questions/45703372/google-sheets-cf7-google-sheets-connector-indirect-cell-reference-issue/45713735?noredirect=1#comment78444075_45713735so, rather than send [hidden payment-amount default:”=If(X12>0,(65+25*Y12),0)”]
I put this in the header of that column.
=ARRAYFORMULA(IF(ROW(A1:A)=ROW(A1),”payment-amount”,IF(ISBLANK(X1:X),,If(X1:X>0,(65+25*Y1:Y),0))))
[hidden payment-amount default:”=If(indirect(“X” & row())>0,(65+25*indirect(“Y” & row())),0)”]That worked fine, but I had another formula that used COUNTBLANK which does not work with an ARRAYFORMULA.
At this point, I realized that I had an issue with formatting not being retained on new rows submitted by Contact Form 7.
So then, in finding a solution to that, I took care of the COUNTBLANK formula issue too.
https://productforums.google.com/forum/#!topic/docs/dlW6U6cHuKw
This solution was to add an additional sheet with array formulas to pull each column over from the original sheet that CF7 submits to. In doing so, you can format a full sheet of cells that will constantly update when the original sheet is populated with new data. This also allowed me to put the COUNTBLANK formula in the top cell of a row with direct cell references and then paste it to the rest of the column so that when new rows are populated, they will use that formula that’s already there.Hope this helps someone else in the future!
So, I did find a couple of solutions (In case it’s helpful to anyone else).
The first was to not send the formulas as hidden variables, but to use ARRAYFORMULAS in the top row of a column.so, rather than send [hidden payment-amount default:”=If(X12>0,(65+25*Y12),0)”]
I put this in the header of that column.
=ARRAYFORMULA(IF(ROW(A1:A)=ROW(A1),”payment-amount”,IF(ISBLANK(X1:X),,If(X1:X>0,(65+25*Y1:Y),0))))
[hidden payment-amount default:”=If(indirect(“X” & row())>0,(65+25*indirect(“Y” & row())),0)”]That worked fine, but I had another formula that used COUNTBLANK which does not work with an ARRAYFORMULA.
At this point, I realized that I had an issue with formatting not being retained on new rows submitted by Contact Form 7.
So then, in finding a solution to that, I took care of the COUNTBLANK formula issue too.
https://productforums.google.com/forum/#!topic/docs/dlW6U6cHuKw
This solution was to add an additional sheet with array formulas to pull each column over from the original sheet that CF7 submits to. In doing so, you can format a full sheet of cells that will constantly update when the original sheet is populated with new data. This also allowed me to put the COUNTBLANK formula in the top cell of a row with direct cell references and then paste it to the rest of the column so that when new rows are populated, they will use that formula that’s already there.Hope this helps someone else in the future!
Hi, Just following up. Can anyone help with this? Any suggestions would be greatly appreciated! Thanks in advance!
Hi, Just following up. Can anyone help with this?