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

0 votes

I'm attempting to use the INDIRECT function to build a dynamic range for a chart. Excel indicates the corresponding range on the sheet, indicating that it recognizes the range I'm creating with INDIRECT:

enter image description here

I get an error message saying the function is not valid when saving the chart,

enter image description here

Does anyone know the solution to the issue or how to make a dynamic chart range with defined start and end points?

Dec 26, 2022 in Others by Kithuzzz
• 27,740 points
56 views

1 answer to this question.

0 votes

Similar to Sean's fantastic response, mine also provides for a start and finish day. To choose the start and end days depending on E2 and E3, first, build two named ranges using Index/Match formulae as follows:

rngDay

=INDEX(Sheet1!$A:$A,MATCH(Sheet1!$E$2,Sheet1!$A:$A,0)):INDEX(Sheet1!$A:$A,MATCH(Sheet1!$E$3,Sheet1!$A:$A,0))

rngValue

=INDEX(Sheet1!$B:$B,MATCH(Sheet1!$E$2,Sheet1!$A:$A,0)):INDEX(Sheet1!$B:$B,MATCH(Sheet1!$E$3,Sheet1!$A:$A,0))

You can then click the series in the chart and modify the formula to:

=SERIES(Sheet1!$B$1,Sheet1!rngDay,Sheet1!rngValue,1)

enter image description here

answered Dec 27, 2022 by narikkadan
• 51,240 points

Related Questions In Others

+1 vote
0 answers
0 votes
1 answer

TypeError: $.ajax(...) is not a function?

Please double-check if you're using the full-version ...READ MORE

answered Feb 22, 2022 in Others by Aditya
• 7,660 points
2,104 views
0 votes
1 answer

Move_uploaded_file() function is not working

The file will be stored in a ...READ MORE

answered Feb 22, 2022 in Others by Aditya
• 7,660 points
952 views
0 votes
1 answer

Dynamic Subtraction formula in excel sheet

Excel is capable of using both a ...READ MORE

answered Jan 6 in Others by narikkadan
• 51,240 points
51 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,640 points
205 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,640 points
364 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,640 points
165 views
0 votes
1 answer

Excel SUM function is not working (shows 0), but using Addition (+) works

The values in A1 and A2 are ...READ MORE

answered Jan 5 in Others by narikkadan
• 51,240 points
47 views
0 votes
1 answer

Is there a way to test a formula result in excel and type it only once, all within one cell and not using a user defined function?

Use the Let function: =LET(Value,A1+B2+C4+G3+B4,IF(Value>10,"No",Value)) I hope this helps ...READ MORE

answered Jan 9 in Others by narikkadan
• 51,240 points
32 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP