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 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, thhe last cell in the range is BG54

If the user types in 10 in the first taab, how can it automatically update the seeries values inn 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 seeries value dialog change thiis =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 annd 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.