Question

When entering line items from Cognito Forms to Google Sheets, how do I reference a specific cell with a formula?


Im new to Zapier, and im going from Cognito Forms to Google Sheets, entering line items for form entries into google sheets, but I need to create an equation, and Im struggling to find a way of referencing a cell in the line its writing to..  for example…. =if(P(ROW())="Yes",(concatenate("T",ROW()-1," - ",)),"").

Its the P(ROW(), in having issues with, cause it needs to be something like P32 if Im on row 32.

The rest of the line is being created from Cognito fields so thats fine.


This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

4 replies

Userlevel 7
Badge +14

Hi @Tracy_1980 

Good question.

One option is to use Airtable instead of GSheets.

Airtable has Formula fields: https://support.airtable.com/docs/formula-field-reference

Airtable Formula fields work within the row and don’t have the row reference issue you’ve encountered with GSheets.

 

OR

 

You can try this GSheets Formula:

=if(indirect("P"&ROW())="Yes",concatenate("T",ROW()-1," - "),"")

 

 

Unfortunately that didnt work.  Its bizaare as the "T",ROW()-1 works, but not the "P"&(ROW()), it doesnt interprete the row in that part of the forumula

 

Userlevel 7
Badge +14

@Tracy_1980 

Try this formula:

=if(indirect("P"&ROW())="Yes",concatenate("T",ROW()-1," - "),"")

 

Userlevel 7
Badge +11

Hey @Tracy_1980! 👋

Just checking in to see whether you were able to get it working using the formula Troy shared? Let us know if you need any further help on this!