TOCOL not updating with new data

[Ross3]

I have a sheet with 3 columns.

In Column A I have a list of dates. These dates are pulled from another sheet in the same workbook with a formula. The formula is copied down the column. I want it to copy the original cell if it encounters a date there and to give an error if it doesn’t. For that I use this formula:

=IF(COUNTA($‘Income Log’.B2)=1,$‘Income Log’.B2,err)

In Column B I have a separate list of dates. These dates are pulled from a third sheet in the same workbook with basically the same method. The following formula is also copied down column B:

=IF(COUNTA($Expenses.A2)=1,$Expenses.A2,err)

In column C I use TOCOL to list all the dates in both other Columns, listing first all the dates in Column A, and then, right under, skipping all the errors, all the dates in Column B.

This is the formula:

=TOCOL(A2:B1048576,3,TRUE)

After hitting enter it turns into an Array formula like this:

{=TOCOL(A2:B1048576,3,1)}

So far so good.

When I add a date to either my “Income Log” or “Expenses” sheet, they appear in the correct place at the bottom of Columns A or B.

Again, so far so good.

However, Column C doesn’t update.
Refreshing doesn’t work either (not that having to manually refresh each time I add data would be the best solution anyway).
The only thing that seem to work is Deleting Column C and rewriting the TOCOL formula. Not a viable solution to have to do this each time I add data, especially taking into account that I have a Pivot table that pulls data from Column C and I’d then have to set that up each time also.

[schiavinatto]

Resize_Arrays_Basic.ods (18,0,KB)

[karolus]

@Ross3

However, Column C doesn’t update.
Refreshing doesn’t work either (not that having to manually refresh each time I add data would be the best solution anyway).
The only thing that seem to work is Deleting Column C and rewriting the TOCOL formula. Not a viable solution to have to do this each time I add data, especially taking into account that I have a Pivot table that pulls data from Column C and I’d then have to set that up each time also.

this is actually an general issue with array-formulas … they cannot resize their output-area automatically.

to workaround that, you may use:

from com.sun.star.beans import PropertyValue as pv

def resize_FormulaArrays(*_):
    doc = XSCRIPTCONTEXT.getDocument()

    frame = doc.CurrentController.Frame
    disp = XSCRIPTCONTEXT.ctx.ServiceManager.createInstance("com.sun.star.frame.DispatchHelper")
    sheet = doc.CurrentController.ActiveSheet
    ranges = sheet.queryContentCells(16) #all Formula[range]

    for single in ranges:
        if (formula:=single.ArrayFormula): # is there a ArrayFormula?        
            single.clearContents(16) # clear
            arg = pv( Name="ToPoint", Value= single[0,0].AbsoluteName)
            disp.executeDispatch(frame, ".uno:GoToCell", "", 0, (arg,))
            arg = pv( Name="Formula", Value = formula[1:-1] )
            disp.executeDispatch(frame, ".uno:InsertMatrix", "", 0, (arg,))  

[Ross3]

Thanks for offering a solution. Unfortunately, I have no idea what to do with this code. I don’t know where or how to use it.

Is there any chance you could walk me through what to do with this as if I was a mentally challenged 5 year old how had barely any technical skills?
It would be much appreciated.

[karolus]

its python … no rocket science!
as beginner, you may need help to organize this stuff:
install apso.oxt
the Documentation: https://gitlab.com/jmzambon/apso

see also https://ask.libreoffice.org/t/how-behave-dynamic-arrays-in-excel-how-is-the-result-referenced-as-a-whole/114154 @sokol92

[Ross3]

hahaha I’m sure Python isn’t rocket science, but I have extremely limited to no experience coding.

Ok, so I’ve installed the apso.oxt extension.

I’ve taken a look at the documentation, and, going to the following menu Tools>Macros>Organize Python Scripts, I have opened the Apso console.

Now, just before I do anything, to avoid me screwing anything up because of doing the wrong thing, is it just a case of pasting your code into the console and running it, and then the array will have turned into a dynamic array?

Edit:

So I just tried and I get an error:
IndentationError: unexpected indent

I tried fixing that (bare in mind I don’t know how to code and I definitely can’t take the time to learn how to code to solve this problem) and I got another error:
NameError: name ‘p’ is not defined

I may be doing everything wrong here, I literally have no idea what I’m doing.

I absolutely get that you’re not here to show me how to code and you did link to documentation, but I honestly don’t know what I’m doing here and, as interesting as all of this is, I simply don’t have the time to learn all these skills, I’m just trying to get a spreadsheet ready for work.

Is there any chance you could walk me by applying this solution step by step?

[karolus]

Ok …
So start with ⇒Tools⇒Makros⇒Organize Python-scripts (shortcut shift alt F11):

Take ⇒Menu⇒New Module and choose something sensefull Name, eg. arrayresizer.py
select this arrayresizer-module and ⇒ Menu ⇒ Edit
this will propably open some Texteditor from your OS, copy the given code from above and paste it into your editor. Store the file (ctrl s) and close the Editor.

Now you may select (from your Calcfile where the Sheet with the formula is focused)
⇒Tools ⇒ Macros ⇒ organize python Scripts ⇒ my Macros ⇒ arrayresizer ⇒ resize_formula_arrays and click on execute

[Ross3]

It all goes well until the Menu>Edit part. It seems like some sort of window wants to popup and then, one instant after, it closes without it completely opening properly.

Do you know if I need to enable some permission or something in Libreoffice to allow it to do this?

[Villeroy]

1. Tools>Options>Security, button [Macro Security…]
   1.1 Choose the highest level of macro security
   1.2 On the second tab add one or more directories where to put documents allowed to run macros. Might be “My Documents” but not “Downloads”.
2. Download the attached document, move it to a trusted directory, open it and click the [Install] button.
3. Tools>Macros>Run… pyCalc>ResizeArrays>resize_FormulaArrays tries to resize all formula arrays in the entire document. It may fail with protected sheets, read-only documents or documents outside trusted directories.
4. You may customize your user interface (toolbar buttons, menu entries, keyboard shortcuts) to make this command more accessible.

[Ross3]

Install what attached document?

[Villeroy]

Excuse me.
ResizeArrayFormulas.odt (35.4 KB)

[Ross3]

Thank you. Did not work though

[CRDF]

@Ross3

Did not work though

Place @karolus script in the path for your OS, run it from sheet
Macro > Run Macro > (select it)

[Villeroy]

@Ross3

Thank you. Did not work though

Resize_Arrays.ods (25.0 KB)

[CRDF]

Do you use this sheet in just 1 machine?
If so you can place the script in a folder in this machine:
[…]/Scripts/python
Go to it and edit script in any text editor.
No need of APSO.
I have never used it.
 
Indentation in Python is mandatory. It takes the role of { […] } in C derived languages;
or blocks BEGIN […] END;
on others.
You must mantain uniformity: TAB or n spaces or…

[Ross3]

Yep, I only use it on one machine.
So how do I use it without APSO?
I’ve created it in notepad, saved it in my machine, and now what?

[CRDF]

@Ross3

saved it in my machine, and now what?

It depends of your OS.
Here in my Linux Ubuntu

home/CRDF/.config/libreoffice/4/user/Scripts/python/

You can Google for W or Mac of course.
It must be saved in this folder.