This content has been marked as final. Show 3 replies
I have a chart that shows typical sales information: Sales, Margin and GM%. GM% is calculated as Margin / Sales. The Sales and Margin data can be for any year the user selects. They also have a series of If statements in their expression looking at other selections the user can make in the document. What I want to do is make the Sales and Margin column heading dynamic, showing the year selected, but also be able to reference the expression label to make the GM% definition relatively easy. For example, my Sales and Margin labels would look something like this:
=(SelectYear) & ' Sales'
=(SelectYear) & ' Margin'
But how do I reference these expressions in my GM% definition calculation? Before I added the year to the column heading I had:
Sales had the label = Sales
Margin had the label = Margin
And GM% had the definition = [Margin] / [Sales]
I don't want to have to repeat the definitions for Sales and Margin again in the GM% definition if I don't have to because they can be a little involved. Is there a way to have the column label be one value and the actual data another value to make referencing it within the chart easy?