Bar chart with bars quantity as an argument

[Maciej]

Hello,
I have series of monthly values and need to create a bar chart, where quantity of bars will be a value stored in a cell - i.e. if the value in the cell is 2 then the chart should show just two bars, while if the value in cell is 5 then the chart should show five bars, etc. I would like that the chart is dynamic and be of adjustable width. I unsuccessfully tried to set dynamic chart data range in Select Data Source window using offset and address functions. Is there a solution to this problem?

 

[Hui]

Assume your data is in A2:A100 and B2:B100
your value 2 is in C2
Setup to Named Formula

XValues: =offset($A$2,,,$C$2)
YValues: =offset(XValues,,1)

Now setup a new chart
Add a new series
Use =Sheet1!XValues or the X values
and =Sheet1!YValues as the Y values

If this doesn't help please post a sample file

 

[Maciej]

Thanks, but ... it doesn't work :-(
Please find example sheet. Excel accept neither formulas nor Named Formulas as the series input (see screenshots).

 

[NARAYANK991]

Hi ,

See the file now.

Narayan

 

[Hui]

Yes it does




See attached file:

 

[Hui]

You were missing the sheet name in the dialoge where you define the Chart values
It must be : =Sheet1!YValues

 

[Maciej]

Thank you guys!