Dynamic chart range using INDIRECT: That function is not valid (despite range highlighted)

by

The way you are trying to do it is not possible. Chart data range has to have a fixed address.

There is a way around this, and that’s using named ranges

Put the number of rows you want in your data in a cell (e.g., E1)
So, using your example, I put Number of Rows in D1 and 6 in E1

In name manager, define the names for your data and titles
I used xrange and yrange, and defined them as:

xrange: =OFFSET(Sheet1!$A$2,0,0,Sheet1!$E$1)
yrange: =OFFSET(Sheet1!$B$2,0,0,Sheet1!$E$1)

now, to your chart – you need to know the name of the workbook (once you have it set up, Excel’s function of tracking changes will make sure the reference remains correct, regardless of any rename)

Leave the Chart data range blank
for the Legend Entries (Series), enter the title as usual, and then the name you defined for the data (note that the workbook name is required for using named ranges)
data points

for the Horizontal (Category) Axis Labels, enter the name you defined for the labels
data labels

now, by changing the number in E1, you will see the chart change:
6 in E14 in E1

More Related Contents:

  1. Can’t pass a range to a lambda using Let and Makearray functions
  2. How to convert to percentage [closed]
  3. Excel Number Separation [closed]
  4. Sequence of numbers based on start and end value of 2 cells [closed]
  5. I have an Excel data sheet that I would like to extract data from into a new sheet
  6. Error in finding last used cell in Excel with VBA
  7. Is the . in .Range necessary when defined by .Cells?
  8. Convert an entire range to uppercase without looping through all the cells
  9. Getting unique values in Excel by using formulas only
  10. Vlookup using 2 columns to reference another
  11. Excel: last character/string match in a string
  12. Referencing value in a closed Excel workbook using INDIRECT?
  13. Simple Pivot Table to Count Unique Values
  14. Excel UNIQUE Across Columns
  15. Ignore Duplicates and Create New List of Unique Values in Excel
  16. Find last used cell in Excel VBA
  17. Two column lookup in table array using INDEX and MATCH
  18. Detect if range is empty
  19. Excel Date to String conversion
  20. If two cells match, return value from third
  21. Excel: Searching for multiple terms in a cell
  22. What does — do in Excel formulas?
  23. Optimizing Excel formulas – SUMPRODUCT vs SUMIFS/COUNTIFS
  24. Countif With Multiple OR Criteria
  25. Copy a selected range to another worksheet
  26. Convert from US to UK format
  27. How to roundup a number to the closest ten?
  28. Convert an Array Formula’s Text Results into a Usable Format
  29. Unique Rank value for a subgroup within a group
  30. VBA: Modify chart data range

Leave a Comment