Mar 01 2020 07:01 PM
Mar 01 2020 11:56 PM
Mar 02 2020 03:32 AM
@Abiola1 Thanks for the reply. I can easily change the data to appearing blank rather than #N/A. It's the graphing that I can't work out what to do. Would love some advice.
Mar 02 2020 03:06 PM
Is there someone that can show me how to do this? I'm having no success working out how to automatically ignore for this graphing.
Mar 02 2020 03:13 PM
You may use dynamic ranges both for X and Y values
with OFFSET or INDEX
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A)-COUNTIF(Sheet1!$A:$A,NA()))
or
=Sheet1!$B$1:INDEX(Sheet1!$B:$B,COUNTA(Sheet1!$B:$B)-COUNTIF(Sheet1!$B:$B,NA()))
and use them in chart like Sheet1!XRange; Sheet1!YRange
Mar 02 2020 04:08 PM
@Sergei Baklan This was looking fantastic but I can't get it to work. No doubt this is just beyond me. I have plenty of other data in the columns so I adjusted your formulas to make a specific range (o33:p48). I also attempted to use the Name Manager to name the ranges, as you did. Then I attempted to edit the series within 'Select Data'. Where am I going wrong? It's just not working. I can see that yours functions perfectly.
I do apologise, but I only dabble in this stuff!
Mar 03 2020 09:33 AM
That's hard to say what exactly is wrong without seeing the file. You may incorrectly calculate dynamic range. You may use relative references in named formulas instead of absolute ones.
Check first how your dynamic range and named formula work within the sheet on formulas, after that apply to the chart.
Mar 03 2021 10:57 PM
@Sergei Baklan I have a similar problem, with the main difference being that my chart data is in rows and not columns. Also, the chart data does not begin in the first column, but in column F and then needs to end at the last column which has data in it. Any suggestions will be welcome.
I want to plot row 7 (dates) and row 35 (currency values) starting from column F up to the last column with data in it with the graph scaling automatically updated as the data range changes.
Mar 03 2021 11:28 PM
Mar 04 2021 12:46 AM
I assume you have something like this
In dates row we have formulas as
=EOMONTH(F7,1)
and in totals row formulas are
=SUM(G8:G34)
We add two named formula for the dynamic ranges
Dates:
=Sheet2!$F$7:INDEX(Sheet2!$F$7:$AA$7,COUNTA(Sheet2!$F$7:$AA$7))
Totals:
=Sheet2!$F$35:INDEX(Sheet2!$F$35:$AA$35,COUNTA(Sheet2!$F$7:$AA$7))
Use them in chart
x-axis:
='Chart Dynamic ranges NA.xlsx'!Dates
Totals data series:
='Chart Dynamic ranges NA.xlsx'!Totals
Please check in Sheet2 of the attached file. If you have another model it's better to illustrate on sample file.
Mar 04 2021 01:30 AM
@Sergei Baklan Thanks for the swift response. The problem is with this is that based on start and end dates the columns will be populated. I've updated your sheet to illustrate this point, see attached. Then the issue is that the formulas in the cells does not work with COUNTA as COUNTA does not see them as empty cells.
Mar 04 2021 07:41 AM
To ignore empty cells and cells with spaces you may use for dynamic ranges formulas
Dates:
=Sheet2!$F$7:INDEX(Sheet2!$F$7:$AE$7,SUMPRODUCT(1*(LEN( TRIM( Sheet2!$F$7:$AE$7)) >0)))
Totals:
=Sheet2!$F$35:INDEX(Sheet2!$F$35:$AE$35,SUMPRODUCT(1*(LEN( TRIM( Sheet2!$F$7:$AE$7)) >0)))
Please check attached.
Mar 04 2021 10:49 PM
@Sergei Baklan that works perfectly. Thanks for your assistance. It is greatly appreciated.
Mar 05 2021 01:02 PM
@Louwrensjfourie , glad to know it helped