Cross Sheet Reference a URL Link
I've got a database sheet listing a bunch of fertilizers and how much to feed of each fertilizer at various stages of growth. During certain stages of growth some fertilizers aren't used so I'm using this sheet to feed reports which builds an easy to read list of what to feed so that any fertilizers not used aren't shown due to the filter I set up on the report. I've published each report so that I could obtain a URL to the report.
On the database sheet, row 1 is a CTRL-K hyperlink to the report for that particular growth cycle. From the Master sheet which tells a cultivator what to feed, I'm trying to create a formula which links to the report when that report's growth cycle is listed on the Master sheet. It works and displays the text of the proper fertilizer list.
Here's the problem I need help with...what comes through on the Master Sheet is only text. The hyperlink doesn't come through so you can't get to the report to see what ferts should be used.
It looks like it's just not possible for cross-sheet references to maintain URLs. Does anyone know of a workaround?
Best Answers
-
Combination of TinyURLs and renaming of a column to a shorter column name gave me the space I needed to make this work but it looks ugly and would look much better if cross-sheet references would maintain URL links from other sheets. It's extremely absurd that they don't already do that and only transmit the text.
-
I agree that being able to maintain the link with the custom text would be ideal.
In the meantime though, you could set up a reference table with the URLs in one column and an identifier in another column then use an INDEX/MATCH to pull the appropriate URL in. Granted it still has the URL display instead of the custom text display, but at least you don't have to fight with an ugly nested IF.
Answers
-
The only thing I can get to work is to include the http URL as a true value in quotes in the IF formula. It's just much uglier than being able to use a CTRL-K value which has a different display name.
-
Ok...that's not going to work. I reached the maximum # of characters for a formula before I finished all of the URL links. This sucks. I'm not sure of another way to do this...
-
TinyURL here I come...
-
Combination of TinyURLs and renaming of a column to a shorter column name gave me the space I needed to make this work but it looks ugly and would look much better if cross-sheet references would maintain URL links from other sheets. It's extremely absurd that they don't already do that and only transmit the text.
-
I agree that being able to maintain the link with the custom text would be ideal.
In the meantime though, you could set up a reference table with the URLs in one column and an identifier in another column then use an INDEX/MATCH to pull the appropriate URL in. Granted it still has the URL display instead of the custom text display, but at least you don't have to fight with an ugly nested IF.
-
Hi Paul. Thanks for the feedback. I'll definitely need to do that to scale the sheet better if we add additional feeding schedules or if the lengthy formula causes any problems or delays on the sheet. Currently I've got it working with TinyURLs which was a pain but it's in place and looks to be working well enough currently.
-
@Mike TV Sure thing.
Have you taken a look through the Ideas topic to see if anyone has submitted it as an enhancement request? If they haven't, feel free to submit it yourself. If they have then feel free to vote it up.
Either way, if you do end up locating/creating something, definitely drop a link in here so that others can locate it and vote on it.
-
Hiya, jumping in here just to say I agree with what was discussed above.
Currently, formulas can only bring in the display text for hyperlinks, so these won't be clickable unless the full URL is spelled out as the display text.
There's a similar Idea post we can vote on / add context to, here:
Help Article Resources
Categories
Check out the Formula Handbook template!
