JosefFreedom
New Member
- Joined
- Oct 19, 2009
- Messages
- 9
I'm trying to find a workaround for one of two issues with Excel Web Services on sharepoint:
1. Cannot show picture in EWS - unlikely to be solved.
2. Cannot use dynamic hyperlink - seems like there might be a creative solution here.
For those of you who have not used Excel Web Services, there is a specific restriction on dynamic hyperlinks, such that what is inside a =Hyperlink() cannot by dynamic. However you can nest Hyperlink() functions and have them work i.e. =if(1=1,Hyperlink(1,"Test"),Hyperlink(2,"Test2")) will work and can be toggled to produce hyperlinks based on changing criteria.
The issue is I'm trying to link to pictures that vary based on a complex set of cubeformula/pivot scenarios. The pictures are named such via parameters - YYYYMMDDCategoryName.jpg - and so in Excel it was super easy to build the filename in a string and pass it into a hyperlink function. This does not however work in Excel Web Services.
The next thing I've been working with is trying to FormulaText() to return the formula of a hyperlink, and then use a named range evaluate() to actually fire the hyperlink. Unfortunately, while this can correctly fire the hyperlink function and could allow a dynamic build, that actual hyperlink returned by the named function is not functional.
So, keeping in mind you cannot change anything inside the Hyperlink() itself - it must be an absolute reference - how can I take a collection of absolute hyperlinks, and bring only the one desired forward to the dashboard? Are there other ideas for how to solve this?
Thanks so much!
1. Cannot show picture in EWS - unlikely to be solved.
2. Cannot use dynamic hyperlink - seems like there might be a creative solution here.
For those of you who have not used Excel Web Services, there is a specific restriction on dynamic hyperlinks, such that what is inside a =Hyperlink() cannot by dynamic. However you can nest Hyperlink() functions and have them work i.e. =if(1=1,Hyperlink(1,"Test"),Hyperlink(2,"Test2")) will work and can be toggled to produce hyperlinks based on changing criteria.
The issue is I'm trying to link to pictures that vary based on a complex set of cubeformula/pivot scenarios. The pictures are named such via parameters - YYYYMMDDCategoryName.jpg - and so in Excel it was super easy to build the filename in a string and pass it into a hyperlink function. This does not however work in Excel Web Services.
The next thing I've been working with is trying to FormulaText() to return the formula of a hyperlink, and then use a named range evaluate() to actually fire the hyperlink. Unfortunately, while this can correctly fire the hyperlink function and could allow a dynamic build, that actual hyperlink returned by the named function is not functional.
So, keeping in mind you cannot change anything inside the Hyperlink() itself - it must be an absolute reference - how can I take a collection of absolute hyperlinks, and bring only the one desired forward to the dashboard? Are there other ideas for how to solve this?
Thanks so much!
