Overview

Excel Links provide a powerful way for users to leverage the full power of Excel to create custom filters that can then be used within their Cosmos functions. A popular user case for this is by creating a wide range of dates for things like Year-to-Date, Month-to-Date, Prior Year, etc. that can then be easily referenced from directly within your Cosmos functions. 

This article covers how to create Excel Links to your reports and leverage them within your Cosmos functions.

Details

Excel Links provide a way for users to enter or manipulate data in Excel while designing a report, "tag" that cell with a name (an Excel Link), and then reference that cell in functions throughout the report. Excel Links can be used across sheets and leveraged in any Cosmos function that exists in the workbook. 

To add Excel Links, select the cell that you want to add an Excel Link for. In the example below, we have a Report Option where the user can input an "As of Date", and then we have a number of cells that are leveraging Excel functions to build out various dates that will be used in the report.

We want to add Excel Links for each of these so they can be used in Cosmos functions, so we will click in the date in cell B1 and then we'll go to the Excel Links section of the Cosmos Excel Add-In.

Next we'll click New to create a new Excel Link and we'll give the Excel Link a name.

You can click a different cell and click Set Range if the cell that the Excel Link is tied to need to be changed, otherwise you can click Save to save the Excel Link. We'll then do the same for the other cells to end up with the following four Excel Links.

Next, when we add an aggregate function to sum something up (Excel Links can also be used in Range Lists), when we add filters we can set the Filter Type to Dynamic and our Excel Links will be able to be use as filters.

This results in us being able to leverage the full flexibility of Excel to create whatever dates we want, and then we can reference these in our Cosmos functions.