I'm trying to find a way to create a single calculated field that can pull in text information from my data into any worksheet regardless of what parameter control is on that worksheet.
I have created 18 parameters to define which data options up on each of 18 dashboards. Each parameter corresponds to one or more calculated fields (based on how I need to format the data) that I use as a filter, and then I use the parameter control as a drop-down menu to select the specific data to view.
I also have text in my source, which I'm pulling into two separate worksheets, "Title", and "Caption", and filtering with the correct calculated field. In cases like the example above, I have a third calculated field, "RJ2-all" that allows the title/caption to pull in information for all three measures.
Rather than doing this 18 times over, it is possible to create one very long calculated field to control all the text on all dashboards? It would need to reference each of the 18 parameter controls so that it could be driven by any of them. My first instinct was to nest the case statements inside IF..ELSEIF...THEN statements, but that doesn't work (because they're strings?). How else could I approach this?
Do you have a workbook that can be shared with the community demonstrating an example of this? Reviewing a sharable workbook might also encourage a solution based on an entirely different approach if the focus is on the desired outcome/view.