How to automatically expand named range with formulas?

[felipepinto]

Do you use menu Sheet > Named Ranges and Expressions or menu Data > Define Range to define a “named range”?

yep i did that. even checked the “insert or delete rows” option

[felipepinto]

@Zizi64

Here is a macro function to adjust a named range:

That is indeed a possibility, i would rather evict macros, but if its a must then we gotta do what we gotta do, on thing tho, since i have differents named ranges i would like to update, how should i modify the function to parametrize the namedrange as string? I tried the following but im getting an error:

REM  *****  BASIC  *****

Function SetRange(sNewAddress as string, sNamedRange as string)

'LoadXray
	oDoc = thiscomponent
	oRange =oDoc.NamedRanges.getByName(sNamedRange)
'Xray oRange
	oRange.setContent(sNewAddress)
SetRange1 = "Check the range by the Navigator."
End function

[Zizi64]

@felipepinto

but im getting an error

Please upload your sample file here.

[felipepinto]

This is the error i get:

Here is the file
test.ods (11.5 KB)

[felipepinto]

Update: i just noticed a typo on the last line SetRange1 should be SetRange as the function was renamed.

but even after the fix the macro doesnt work

[Zizi64]

NamedRanges are not same as the DataBaseRanges. You have not a NamedRange with the name “data1” in your document.

Do not use Excel exclamination sign as a formula separator.
I never tried my macros for Named Ranges with R1C1 type references.

[felipepinto]

Do not use Excel exclamination sign as a formula separator.

What would you suggest to use instead?

I never tried my macros for Named Ranges with R1C1 type references.

Hopefully this is not a problem, after i started coding on basic A1 references stopped making sense to me.

NamedRanges are not same as the DataBaseRanges. You have not a NamedRange with the name “data1” in your document.

Well then that means that updating the formula to search on the right place would fix right?

[felipepinto]

Im getting a new error:

this is the updated formula:

REM  *****  BASIC  *****

Function SetRange(sNewAddress as string, sNamedRange as string)

'LoadXray
	oDoc = thiscomponent
	odbRange =oDoc.DataBaseRanges.getByName(sNamedRange)
'Xray oRange
	odbRange.setContent(sNewAddress)
SetRange = "Check the range by the Navigator."
End function

[Zizi64]

@felipepinto

Do not use Excel exclamination sign as a formula separator.

What would you suggest to use instead?

The regular “.” (dot)

[felipepinto]

The regular “.” (dot)

I dont understand, the dot doesnt work:

(the bottom two cells are =counta(indirect(R1C)))

[Zizi64]

@felipepinto

Well then that means that updating the formula to search on the right place would fix right?

.
I never handled DataBaseRanges with macros. I do not know how to get the DataBaseRanges by macro. My macro is for the NamedRanges only.
Install an object inspection tool: MRI or the XrayTool. They can list the properties and methods of the programming objects…

[Villeroy]

Ordinary references, named ranges, database ranges, chart ranges, validation ranges, conditional formatting, source ranges of form controls expand when you insert new rows and columns.
When option “Expand references …” is NOT checked:

• References shift down when you insert rows on top of the reference.
• References do not expand when you insert rows directly below the reference.

References expand regardless of that option when you insert rows anywhere below the top and the bottom of a range.
Columns show the same behavior transposed.

[felipepinto]

I added a breakpoint and got the properties:

Now i just need to figure out how to define that com.sum.star.table.CellRangeAddress into the range i want to set

[felipepinto]

When option “Expand references …” is NOT checked:

Ok so i dont need macros, if it works thats good, imma try.
Another question: does formulas expand as well?

[Zizi64]

Here is some sample code for handling data base ranges:

Macro to take base data from DataSource to to Calc

@Ratslinger is there a way of refreshing all the ranges in a spreadsheet with a variation on the macro you referred to in Calc macro failing to update sheet data from Base query - #3 by Ratslinger please? I have a spreadsheet that imports the results of 5 queries from one database. All the queries use the same :AppraisalID criterion to select one record and it’s related linked records in other tables. I cannot use a single query partly because it would generate masses of unnecessary data and pa…

[felipepinto]

@felipepinto

Ok so i dont need macros, if it works thats good, imma try.

Checked or not checked that option, insertng a new cell imediatetly at the bottom of a range does nothing.

[felipepinto]

@Zizi64

Here is some sample code for handling data base ranges:

Thanks for referencing, i just checked on that, unfortunately that thread is more about getting the range of a database intead of setting it, it does set the range for a chart tho.

[Villeroy]

@felipepinto

Another question: does formulas expand as well?

Any reference in formula expressions expands automatically after insertion of new rows/columns.
There is one exception: If the reference includes only one row/column, nothing expands. SUM(A1:A99) does not expand when you insert an adjacent column, but SUM(A1:B99) does.

[felipepinto]

It not what i meant, lemme explain with example:
sheet1 have a named data range that goes R1C1:R30C10 at column C10 all rows have the same formula (=RC[-1]+RC[-2]),
so if i look at for example row 10 i expect and see that the cell R10C10 have the formula above.
Now when i insert a new text on the cell R31C1 which would be a new row, i would expect that the named data range would be updated to the new range: R1C1:R31C10 and that on the cell R31C10 the formula would be copied along

[Villeroy]

i would expect that the named data range would be updated to the new range: R1C1:R31C10

Yep

and that on the cell R31C10 the formula would be copied along

No

https://forum.openoffice.org/en/forum/download/file.php?id=46813 performs both steps.

• Detects the current region of adjacent non-blank cells around the current range selection, so you just need to select some cell(s) in your list.
• Turns on “Expand references…” if it is turned off.
• Inserts as many rows into the current region as selected.
• Copies down any formulas from the row above.
• Selects remaining blank cells for editing.
• Turns off “Expand references…” if it was turned off.

A second macro deletes selected rows from the current region.