You can create a table calc that uses PREVIOUS_VALUE to cycle through all the "A" rows and build a string. An example for doing that is here:
If you struggle to implement that, upload a sample workbook and I'll help you.
this looks perfect but I have small issue with the implementation in my DB.
Right now it is just copy the URL substring just into the filed "Join String" because it is always running into the IF clause and never in the Else clause of your calculation.
- My Team names are actually Strings e.g. "Calendar" or "Internet & Mail"
- My Substring is of the format "String" as well.
something like this:
One additional question, storing this link in something like a variable - is this possible?
This calculated field join string is also a variable. What exactly do you mean by variable .What you want to do ?
Now I have these values for team A, B, C and so on.
I would like to use them separately for e.g. putting them into a text.
Sorry for being so imprecise, trying it again.
Now that I have this string which is in reality an url I would like to use this url (for each team one url) somewhere else, clickable.
e.g. a text or something like "A has 5 items" where the 5 contains the link.
That's a table calc. A table calc is only good in the sheet where it is computed. If you want to use it elsewhere, you'll have to re-calc it there. (Which means those rows will need to be in the underlying table on the sheet where it is being used.)
You can create a URL action which will create a clickable link in Tooltip or Direct click on Select/Hover. Follow below steps:
1. Press CTRL+SHIFT+A
2. Add action--->Go to URL
3. Run action on---> Hover/Select/Menu As per your requirement, select any one from these 3 options.
4. Select your calculated field which contains URL as below and Click Ok.
4. Now when you click on field on your sheet, it will open the browser with the link depending upon the action you selected(Hover/Select/Menu).
Hope this helps.