indirect cell references seem to be causing formulas to not be sent to my sheet.

  • Resolved sillycar_wp

    (@sillycar_wp)


    7 years, 3 months ago

    Hi There. I love your plugin first off! Thanks!
    I have a major issue though. I did search first and am sorry if I missed a thread that covered this….

    I’m sending some hidden fields to my sheet and they include formulas.

    Here’s an example of my issue.
    If I send this field below.
    [hidden payment-amount default:”=If(X12>0,(65+25*Y12),0)”]
    It sends to my google sheet fine, but I need a row relative reference. So, not X12 every time, but column X of the current row, and not Y12, but column Y of the current row.

    When I try to do this using the alterations below (this formula works fine if I type it directly into my cell of my google sheet so I don’t think there’s an issue with the formula)
    [hidden payment-amount default:”=If(indirect(“X” & row())>0,(65+25*indirect(“Y” & Row())),0)”]
    I get an empty cell for that field in my sheet.

    Is there something I am missing, or can you offer any advice on another method to accomplish this?

    I have three total fields that are having the same issues.
    Below are the other two with and without indirect references to the cell.

    This one works, but is not what I need.
    [hidden divisions-more-than-one default:”=IF(X13>0,X13-1,0)”]

    This one is what I need, but leaves an empty cell in my sheet.
    [hidden divisions-more-than-one default:”=IF(indirect(“X” & row())>0,indirect(“X” & row())-1,0)”]

    This one works, but is not what I need.
    [hidden number-divisions-requested default:”=10-COUNTBLANK(H10:Q10)”]

    This one is what I need, but leaves an empty cell in my sheet.
    [hidden number-divisions-requested default:”=10-COUNTBLANK(indirect(“H” & row()):indirect(“Q” & row()))”]

    Thanks in advance for anything you can do to help!

Viewing 4 replies - 1 through 4 (of 4 total)
  • Thread Starter sillycar_wp

    (@sillycar_wp)

    Hi, Just following up. Can anyone help with this?

    Thread Starter sillycar_wp

    (@sillycar_wp)

    Hi, Just following up. Can anyone help with this? Any suggestions would be greatly appreciated! Thanks in advance!

    Thread Starter sillycar_wp

    (@sillycar_wp)

    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_45713735

    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!

    Plugin Author westerndeal

    (@westerndeal)

    @sillycar_wp
    Happy to hear that issue is solved from your side only,
    so there was an issue with google sheet, not with the plugin,
    let us know if you have any further issues with the CF7 Google Sheet Connector Plugin.

    Thanks
    Abdullah

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘indirect cell references seem to be causing formulas to not be sent to my sheet.’ is closed to new replies.