Find last number entry

[Steve_in_Kent]

Am using this formula, in a named variable

=OFFSET(Log!$G$6,COUNTA(Log!$G:$G)-1,0,-MIN(chtlen,COUNTA(Log!$G:$G)-1),1)

For a dynamic chart., however, range(G:G), however, if a formula is put into say row G47, the dynamic chart, adds the entry, even if the formula is NA(),
at the moment its a null and it doesnt do anything.

So is there a way, of adding a formula that creates a null, OR, more likely, do i need to change that OFFSET forumula, to only count if its a number.. something like

=OFFSET(Log!$G$6,COUNTA(ISNUMBER(Log!$G:$G)-1,0,-MIN(chtlen,COUNTA(ISNUMBER(Log!$G:$G)-1),1)

(silly example to give you the idea)

[HansV]

Instead of COUNTA(Log!$G:$G), use SUM(--ISNUMBER($G:$G))

[Steve_in_Kent]

Heh, i was close...
I changed that chtCAT variable to:-
=OFFSET(Log!$G$6,SUM(--ISNUMBER(Log!$G:$G))-1,0,-MIN(chtlen,SUM(--ISNUMBER(Log!$G:$G))-1),1)

now.. when the sheet is click on , and the code fires i get
Error 1004
application defined or object defined error.. and it errors out on this line:-
Private Sub Chart_Activate()

Dim srsSeries As Excel.Series
Dim rngLabels As Excel.Range
''' Get a reference to the chart series we want to label.
Set srsSeries = Charts("Graph").SeriesCollection(2)
''' Get a reference to the range containing the data labels.
Set rngLabels = Worksheets("Log").Range("MyLabel")
'Set rngLabels = ActiveWorkbook.Names("MyLabel").RefersToRange

''' Call the XY Chart Labeler to label the series.
Application.Run "XYChartLabeler.xlam!LabelChartSeries", srsSeries, rngLabels, xlLabelPositionInsideBase

End Sub

[HansV]

How is MyLabel defined?

[Steve_in_Kent]

And, when i click on the formula, it correctly identifies, the new entry.. but for some reason the graph isn't updating.

[Steve_in_Kent]

=OFFSET(chtCATS,0,-3)

[Steve_in_Kent]

The formula looks as though its working ok. when i click on the formula in the name area, it correctly highlights, the new 43 row.

however, the label add on, then suddenly stopped showing the name, but the percentage instead.

and although there IS data in the 43 area.. its not showing a thing..

HMMM!

i think maybe i need to change the ThreeWeekCAT forumla as well

[Steve_in_Kent]

Ok.. sort of figured out a bit.. the Standard deviation is going ballistic.. hence the ''Squish''.. so the Y axis was set to auto.

however, not sure why the labels have suddenly gone to numbers!

[HansV]

Make sure that the column offset in the definition of MyLabel is correct.

[Steve_in_Kent]

Yes, it hadn't changed Hans.. since it was working correctly..

I added another chtCAT2, based on the COUNTA function, and click on it, and it correctly highlights the names that go in the columns. !!
but it doesn't show them... atm

[HansV]

I'm afraid I'd have to see a sample workbook to see what's going on.

[Steve_in_Kent]

Yes, i figured as much hans.. the workbook is getting messy !!!

Will have another crack at it, on monday,, and see how i go.. otherwise, will chop it down in size, and would be extremely grateful when you get a minute to look at it.

thanks again

steve