Excel 2010 formula in series values, need to update as user specifies I have a n

Question

Excel 2010 formula in series values, need to update as user specifies

I have a number of graphs in the Excel 2010 spreadsheet that I am working on.

I have a cell in the first taab where the user types a number...

I would like all of the graphs to show the same number of line items depending on the number that they have chosen in the first tab.

for example, I have a chart with a "series values" of

=FY!$BG$3:$BG$54

This points back to the FY tab, the first cell is BG3, the last cell in the range is BG54

If the user types in 10 in the first tab, how can it automatically update the series values in the graph just mentioned to

=FY!$BG$3:$BG$12

Please advise!

Explanation / Answer

Hello,

you need to create a dynamic range name that will refer to the correct range with a formula. Then plug that range name into the chart...

First, select the cell where the user enters the number and give that cell a range name, like "ShowWeeks" (without the quotes). You can type that name in the name box above cell A1 and hit enter.

To create the range name for the series:

click Formulas Ribbon > Name Manager >> New

Enter a name, for example dynamicSeries1

Leave the scope at "Workbook"

In the "Refers to" box enter the formula

=offset(FY!$BG$3,0,0,ShowWeeks,1)

This formula creates a range that starting from cell FY!$BG$3 is in the same row, same column, as many rows high as the number in the cell called ShowWeeks, and one column wide.

To use the dynamic range name in the chart

Edit the chart data source and in the series value dialog change this

=FY!$BG$3:$BG$54

to

=FY!dynamicSeries1

The range name must be preceded by the sheet name or the workbook name when you enter it.

Now the user can enter a different number in the ShowWeeks cell. The formula for dynamicSeries1 will recalculate immediately and the chart using that range name will update..

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.