My sheet integration formula not working, need help

My form 52745887899988 is integrated with google sheets.

https://docs.google.com/spreadsheets/d/1-4VBKaVZZGqDfAcroVyc4-Bne8GgjI8DfB0bt6vKzys/edit#gid=0

I have column AG  to combine the CAT number with the Edit Submission link with this formula, 

=ArrayFormula(HYPERLINK("https://jotformpro.com/edit/"&AE2:AE,"Edit-"&AF2:AF))

The formula should grab the link and add text "Edit-" to the cat number and shows "Edit-1"

But it is not working, I don't get the submission number.

The idea is to have the Form CAT number as the edit submission link as well.

I believe the problem in the formula is after the /edit/ ,it has no submission number to work with. 

Any help appropriated...

Thank you Robert

You will need the form number and the submission number. Also you will need to specify the mode at the end. Try this in your spreadsheet:

=HYPERLINK("http://www.jotform.com/form.php?formID=52745887899988&sid="&B2&"&mode=edit", "Edit-1")

You should be able to figure out the rest yourself.

Hi Aubrey,

The first cell works fine but I require an array to fill the column. Dragging the formula is an option but not preferred. I tried a couple of options but none of them worked, the problem is trying to get the formID into the ARRAYFORMULA by using the column Edit Submission which works on its own but when used in a formula it brakes.. So my next thought was adding the CAT number to the Edit Submission, "Edit Submission-1".

=HYPERLINK("https://jotformpro.com/edit/3762313831515311129","Edit Submission")&"-"&(AF2:AF)

As soon as I added &"-"&(AF2:AF) the link brakes, so far no luck.

Thanks for the option.

regards, Robert

Let me do further test on how to get this working, I will get back to you once I have a solution.

Please reply to this ticket after some time so it will get updated.

Hi Marvih,

Thank you, there is no rush on this...

Here is another formula that failed:

=ARRAYFORMULA(JOIN(AE2:AE," ",AF2:AF))

AE = Edit Submission, AF = CAT No.

I get a String of Text "Edit Submission 1Edit Submission 2" and so on.

Best Regards, Robert

I got it to work like this:
=HYPERLINK("http://www.jotform.com/form.php?formID=52745887899988&sid="&B2&"&mode=edit", "Edit-"&D2)

And then dragged the formula.

For example:

Hi,

OK I can use this for now but I really need an Array formula so it populates the column without my having to drag it down every time a new submission comes in. I suppose I could drag it down a long way and leave it at that. perhaps your colleague Marvih,may find a solution also. 

FYI I had to change the columns in the formula.

=HYPERLINK("http://www.jotform.com/form.php?formID=52745887899988&sid="&AF2&"&mode=edit","Edit-"&AF2)

  It works fine.

see column AG

https://docs.google.com/spreadsheets/d/1-4VBKaVZZGqDfAcroVyc4-Bne8GgjI8DfB0bt6vKzys/edit#gid=0

and column A where =IMPORTRANGE is used.

https://docs.google.com/spreadsheets/d/1tRDd0VhKK8PuusshSAccYfyYpdeD7A5gBe_7lU5I-f0/edit#gid=0

Thank you, Robert

I'm afraid that I didn't use "ArrayFormula" until now, so I will have to work on this and get back to you Robert.

Thank you for your patience.

Hi, 

After I checked further the formula Aubrey offered did not work. It gives the same submission number for every row. 

=HYPERLINK("https://jotformpro.com/edit/3762313831515311129="&P2&"&mode=edit", "Edit-"&P2)

I'm working on another option with a new sheet;

CA Report 

Column B has the HYPERLINK 
Column P has the number,
Column Q has what should be the result. 
Q2 cell has the formula above and is for that row only;

Thanks, Robert

I have checked the last sheet you shared and it seems like you already got it working: 

I was also testing on my end and got this working: 

=ArrayFormula(HYPERLINK("https://jotformpro.com/edit/"&AD2:AD, "Edit-"&B2:B))

Here's my cloned sheet from yours (the very first one you shared): https://docs.google.com/spreadsheets/d/1tmNr3-O4J0IhSvvrsmfePCJ8SrcPY0d12qTaCxyNitE/edit?usp=sharing 

Find the formula on the "AG" column.

Also, please note that it's not recommended to alter the integrated sheet with any formula or do any change on it since that could break the integration and your spreadsheet may stop updating, I would recommend you to create a copy and use the "IMPORTRANGE" function to get the data synced and apply any change on this sheet instead. 

I hope this helps. 

Hi,

I will give your formula a try, I think you have the answer, the formula I have there is not working, it gives the same submission for each row.

Many thanks, Robert

PS Yes I have learned the hard way about messing with the integrated sheet...

RBC