[Solved] Dynamically change Chart Data Ranges

[FKlusmann]

Hi, and welcome to the forum.

Thank you, sir.

… a [chart] of more than the last 10 test becomes meaningless. How can the X-axis be adjusted?

Though the topic is marked Solved, there seem to be additional questions. Perhaps the attached method will be of interest. It does not use evil macros.[/quote]
Very interesting, sir!
As a test, I deleted A33, and see the dreaded dip in the graft. Replacing " IF(ISBLANK(A" with " IF(ISBLANK(B" >>> it is column "B" which may hold non-continuous data <<< caused the counter to restart. It restarted in rows 31, and 19. Therefore 19 points are plotted, going back to 10/11/13 on the X-axis.
Now, if I can figure out how to automate the filter (Data, Autofilter, "HCT", Not_Empty..) and copy the visible data I would be happy. Yes, I have done this with an (evil) macro in Excel 2002.

Thank you for showing me how to create a bottom-up index!

[Villeroy]

Quick and dirty copy-filter-to-other-sheet macro?

Code: Select all

REM  *****  BASIC  *****
Const cDBRange = "Data" 'name of database range
Const cFieldIndex = 2 'filter 3rd column of the db range
Const cTargetSheet = 0 'copy result to first sheet

Sub CopyFilter()
dbr = ThisComponent.DatabaseRanges.getByName(cDBRange)

aTarget = createUnoStruct("com.sun.star.table.CellAddress")
aTarget.Sheet = cTargetSheet

fld = createUnoStruct("com.sun.star.sheet.TableFilterField")
fld.Operator = com.sun.star.sheet.FilterOperator.EMPTY
fld.Field = cFieldIndex

fd = dbr.getFilterDescriptor()
fd.FilterFields = Array(fld)
fd.CopyOutputData = True
fd.OutputPosition = aTarget

rg = dbr.getReferredCells()
rg.filter(fd)
End Sub

Adjust the 3 constants.

[MrProgrammer]

Now, if I can figure out how to automate the filter (Data, Autofilter, "HCT", Not_Empty..)

Simple formulas; no evil macros.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[FKlusmann]

Quick and dirty copy-filter-to-other-sheet macro?

Thank you, Villeroy!
This finds the correct values. I have not found the syntax assign the output to a cell range (searching both OpenOffice and LibreOffice). Please point me to a URL which teaches the syntax.

Also, my Excel macro in Libreoffice Calc dies at "Selection.Copy" with Runtime error '1' - Type: com.sun.star.uno.RuntimeException Message: That command cannot be used on multiple selections

[FKlusmann]

Now, if I can figure out how to automate the filter (Data, Autofilter, "HCT", Not_Empty..)

Simple formulas; no evil macros.

Whoa!, I never considered using Median for anything like this!
First, Find out which cells have values and count with ISNUMBER. That is nice.
(There are 15 over a span of in 21 rows in my modified sheet.)
Second, Use IF to identify a change in sequence provided by the first test.
Third, Use Median to mark the wanted rows "True" (and change the number desired here too!)
Then Copy the desired values based on Median's "True or False"
And Plot the graft!
MrProgrammer, You definitely thought outside of my box.
Thank you!!!!!!!!!

Simple formulas

Yes, used in unique ways by an outstanding helper, MrProgrammer.