Hi All,
I am trying to create a dynamic chart that updates based on user-inputs for the x-axis and y-axis. I have a working lookup table based on the user inputs (i.e., user has a dropdown menu to select x-axis or y-axis variables, and then an off-tab lookup table updates based on those selections). The chart is properly running off of the inputs on that table.
The challenge is that some of the user-input selections are percentages (in the form of ##.#%), while others are absolute numbers (in the form of ###,###). If I set the formatting of that column on the lookup table to percentage or absolute number, the output chart axis label looks strange if the other variable is selected (i.e., the chart shows all the absolute number in % form or vice versa).
I have tried using a text() function around the lookup to work like this: =TEXT([lookup function], IF([selection = percentage],"##.#%","###,###"))
This is working in making the numbers in the table look right for if they're absolute or percentages, but it doesn't seem to be carrying over into the chart axes. In fact, it seems to really be messing up the chart axes to the point that it doesn't change at all whether I select the absolute input or the percentages input (it should and otherwise does, without the text() function added).
Do you have any suggestions for how I might be mis-using the TEXT() function or how I could maybe use some other conditional formatting to get the results I need? Thank you very much Excel community!
I am trying to create a dynamic chart that updates based on user-inputs for the x-axis and y-axis. I have a working lookup table based on the user inputs (i.e., user has a dropdown menu to select x-axis or y-axis variables, and then an off-tab lookup table updates based on those selections). The chart is properly running off of the inputs on that table.
The challenge is that some of the user-input selections are percentages (in the form of ##.#%), while others are absolute numbers (in the form of ###,###). If I set the formatting of that column on the lookup table to percentage or absolute number, the output chart axis label looks strange if the other variable is selected (i.e., the chart shows all the absolute number in % form or vice versa).
I have tried using a text() function around the lookup to work like this: =TEXT([lookup function], IF([selection = percentage],"##.#%","###,###"))
This is working in making the numbers in the table look right for if they're absolute or percentages, but it doesn't seem to be carrying over into the chart axes. In fact, it seems to really be messing up the chart axes to the point that it doesn't change at all whether I select the absolute input or the percentages input (it should and otherwise does, without the text() function added).
Do you have any suggestions for how I might be mis-using the TEXT() function or how I could maybe use some other conditional formatting to get the results I need? Thank you very much Excel community!
