Weird one: Automatically generate a Link in a column to the sheet I am in...

Josh Reed

Hey all,

I have an interesting ask. My company uses sheets we call Roster files to organize HCP Contracts for Medical Events. The request from my team is to have a column in these roster files that populates with a link to the roster file they are already in.

Why? We've drafted reports to organize all HCP contracts grouped by various filters (Coordinator, Entity, Therapeutic Area Etc.). It would be convenient to have a link to the Roster sheet itself to edit some of the fields in the Roster that do not play well in reports (Some of it is vertically oriented rather than horizontally like SS likes, so the variance in data in certain columns renders them useless in some instances). What they do now is have a Medical Event Master Tracker open which contains the links to the individual Roster sheets along with high level data pertaining to the given event. That sheet is incredibly slow and cumbersome to use (despite aggressive archiving), so we are looking for alternative solutions.

My first thought, of course, was index/match the ID tag on the Master Tracker, but that will not return the link, just the text. TBH I have no idea where to begin with this and think it might be a nonstarter period. Maybe using an API to leverage scripting or something but that's a bit beyond my current capabilities, let alone time constraints.

Any and all ideas are welcome... how do you autogenerate a link in a sheet to itself? lol

I'm thinking... these RFs need to be manually created (until I have time to set this workspace up in control center)... I'll probably just have to manually copy/paste the url at the top all the way down the column where the HCP rows are. Not ideal, obviously, but maybe my only/best solution for now.

Dianna

I am not sure if you have the link to the reference sheet or cell but if you know what the url is and can import/ input that, use a helper column with a formula

="Https://" + [url reference]@row where [URL reference]@row is the URL reference you need.

You may be able to combine the index match formula with above to use one cell instead of two.

Josh Reed

Hi Dianna,

Thank you for the suggestion this does give me something to think about... It doesn't need to be an actual link I guess, as long as the URL is there they can copy it and paste into a new tab/window.

We do have the full URL hyperlink to the Roster pasted into one of the columns on the master tracker so I could index/match that into a hidden column on the Roster sheet and then use that cell to fill in the formula you suggested down to the granular level (per row). This is advantageous in that the roster sheets are already at the max for referencing other sheets (in the process of pairing it down, but using an absolute reference to the one cell index/matched rather than doing it down a whole column is MUCH better)