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 tab where the user types a numbeer. I wouuld 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 "seeries 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 Pllease 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 onee 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!dynamicSeries11

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

Now the user can enter a different number in the ShowWeeks ccell. The formula for dynamicSeries1 will recalculate immediately and the chart usingg 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.