- AgilityAssoc.CanadaAsked on November 08, 2017 at 01:38 PM
My form 52745887899988 is integrated with google sheets.
I have column AG to combine the CAT number with the Edit Submission link with this formula,
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
- JotForm SupportaubreybourkeAnswered on November 08, 2017 at 03:19 PM
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:
You should be able to figure out the rest yourself.
- AgilityAssoc.CanadaAnswered on November 08, 2017 at 04:14 PM
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".
As soon as I added &"-"&(AF2:AF) the link brakes, so far no luck.
Thanks for the option.
- JotForm SupportMarvihAnswered on November 08, 2017 at 05:49 PM
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.
- AgilityAssoc.CanadaAnswered on November 09, 2017 at 09:55 AM
Thank you, there is no rush on this...
Here is another formula that failed:
AE = Edit Submission, AF = CAT No.
I get a String of Text "Edit Submission 1Edit Submission 2" and so on.
Best Regards, Robert
- JotForm SupportaubreybourkeAnswered on November 09, 2017 at 10:08 AM
I got it to work like this:
And then dragged the formula.
- AgilityAssoc.CanadaAnswered on November 09, 2017 at 11:41 AM
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.
It works fine.
see column AG
and column A where =IMPORTRANGE is used.
Thank you, Robert
- JotForm SupportNik_CAnswered on November 09, 2017 at 12:41 PM
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.
- AgilityAssoc.CanadaAnswered on November 10, 2017 at 06:38 AM
After I checked further the formula Aubrey offered did not work. It gives the same submission number for every row.
I'm working on another option with a new sheet;
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;
- JotForm SupportKevin_GAnswered on November 10, 2017 at 08:54 AM
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:
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.
- AgilityAssoc.CanadaAnswered on November 10, 2017 at 11:16 AM
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...
- AgilityAssoc.CanadaAnswered on November 10, 2017 at 11:38 AM
Excellent Work As Usual