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)
Find last number entry
-
- 4StarLounger
- Posts: 419
- Joined: 04 Feb 2010, 11:46
Find last number entry
----------------------------------------------------------------------------------------
Owing at LEAST 34 beers to other helpful forum members. Especially HansV!
Owing at LEAST 34 beers to other helpful forum members. Especially HansV!
-
- Administrator
- Posts: 78494
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Find last number entry
Instead of COUNTA(Log!$G:$G), use SUM(--ISNUMBER($G:$G))
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 419
- Joined: 04 Feb 2010, 11:46
Re: Find last number entry
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
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
----------------------------------------------------------------------------------------
Owing at LEAST 34 beers to other helpful forum members. Especially HansV!
Owing at LEAST 34 beers to other helpful forum members. Especially HansV!
-
- Administrator
- Posts: 78494
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 4StarLounger
- Posts: 419
- Joined: 04 Feb 2010, 11:46
Re: Find last number entry
And, when i click on the formula, it correctly identifies, the new entry.. but for some reason the graph isn't updating.
----------------------------------------------------------------------------------------
Owing at LEAST 34 beers to other helpful forum members. Especially HansV!
Owing at LEAST 34 beers to other helpful forum members. Especially HansV!
-
- 4StarLounger
- Posts: 419
- Joined: 04 Feb 2010, 11:46
Re: Find last number entry
=OFFSET(chtCATS,0,-3)
----------------------------------------------------------------------------------------
Owing at LEAST 34 beers to other helpful forum members. Especially HansV!
Owing at LEAST 34 beers to other helpful forum members. Especially HansV!
-
- 4StarLounger
- Posts: 419
- Joined: 04 Feb 2010, 11:46
Re: Find last number entry
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
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
You do not have the required permissions to view the files attached to this post.
----------------------------------------------------------------------------------------
Owing at LEAST 34 beers to other helpful forum members. Especially HansV!
Owing at LEAST 34 beers to other helpful forum members. Especially HansV!
-
- 4StarLounger
- Posts: 419
- Joined: 04 Feb 2010, 11:46
Re: Find last number entry
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!
however, not sure why the labels have suddenly gone to numbers!
----------------------------------------------------------------------------------------
Owing at LEAST 34 beers to other helpful forum members. Especially HansV!
Owing at LEAST 34 beers to other helpful forum members. Especially HansV!
-
- Administrator
- Posts: 78494
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Find last number entry
Make sure that the column offset in the definition of MyLabel is correct.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 419
- Joined: 04 Feb 2010, 11:46
Re: Find last number entry
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
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
----------------------------------------------------------------------------------------
Owing at LEAST 34 beers to other helpful forum members. Especially HansV!
Owing at LEAST 34 beers to other helpful forum members. Especially HansV!
-
- Administrator
- Posts: 78494
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Find last number entry
I'm afraid I'd have to see a sample workbook to see what's going on.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 419
- Joined: 04 Feb 2010, 11:46
Re: Find last number entry
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
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
----------------------------------------------------------------------------------------
Owing at LEAST 34 beers to other helpful forum members. Especially HansV!
Owing at LEAST 34 beers to other helpful forum members. Especially HansV!