Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Trouble with Charts

Copper Contributor

Hello - I am trying to set up a chart that updates dynamically based on the value of one column (y axis).  I need to have the week 1-52 in the second column (x axis), but because those cells already have data, the chart is plotting all 52 weeks.  I only want the chart to plot the weeks as data is entered into the y axis chart.

Nezzy21_0-1701378030992.png

Nezzy21_1-1701378048814.png

 

SO when data is in week 1, the chart only shows week 1 - it doesn't show all 52 weeks with one dot.

 

5 Replies
One way to create a dynamic range for your x-axis is by using the OFFSET and COUNTA functions. For example, if your x-axis data starts from cell A2, you might use a formula like =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A),1)

@Excelonlineadvisor Thank you. I realize I made an error in my original message and got the x and y axis backwards. I'll edit to fix. I hope my explanation and photos below can add some clarity. I added the formulas as you described, but I'm still seeing all of my X Axis and I only want to see the X Axis as data is entered into Y. Below is how it currently looks:

Nezzy21_0-1701387568239.png

Nezzy21_1-1701387608845.png

Because I only have up to week 5 entered in the 'Meat' column (my y axis), I only want the chart to display up to Week 5 as the image below:

Nezzy21_2-1701387743489.png

 

 

 

best response confirmed by Nezzy21 (Copper Contributor)
Solution

@Nezzy21 

Here's a demo that may work for you:

Patrick2788_0-1701459504013.png

 

The trick with the dynamic ranges is to have the 'height' of both determined by the 'Meat' column.  Also, it's ok to use OFFSET for the dynamic items for charting purposes.  Charts do not take kindly to dynamic items created with TAKE.

Wow! This is perfect! Thank you!
You're welcome!
1 best response

Accepted Solutions
best response confirmed by Nezzy21 (Copper Contributor)
Solution

@Nezzy21 

Here's a demo that may work for you:

Patrick2788_0-1701459504013.png

 

The trick with the dynamic ranges is to have the 'height' of both determined by the 'Meat' column.  Also, it's ok to use OFFSET for the dynamic items for charting purposes.  Charts do not take kindly to dynamic items created with TAKE.

View solution in original post