[Dropped] Manually refresh cell references

[heskencren]

Hello lovely Open Office people,

I have a spreadsheet with an area of "fixed" reference data fields above the columns and rows of the table area.
The nitty gritty of the calcs takes place in the table as usual.

One row (RowC) in the fixed data field area references (Copies) the cell data from one row (RowR) in the table below it.
This becomes the comparator entry.

A calculation column (ColumnZ) in the table is performed to compare each row in the table to data in RowC.

As expected, if I then arrange or sort the table (ascending or descending) according to the ColumnZ the Z values change because the referenced RowR's contents change.

What I need is to cut that loop or feed-in link to RowC before I arrange or sort so that the RowC is not updated after the sort. So some sort of button I press to only update that RowC when required. So the contents of RowC REMAIN THE SAME until an update button or something is triggered maybe.

If I do an "IF" logic based on a trigger cell being 1 or 0 then the row RC updates as zero rather than remaining unchanged. What I need is an ELSE unchanged function.

Ideally I need an additional column in the table area that when one of its cells is triggered, like a button, it puts that row's data into row RC and that data stays there until a different button is pushed.

Any ideas please?

Thankyou

[FJCC]

Please upload a Calc document that demonstrates what you want to do. To upload a file, click Post Reply and look for the Attachments tab just below the box where you type a response.

[MrProgrammer]

One row (RowC) in the fixed data field area references (Copies) the cell data from one row (RowR) in the table below it.

In a spreadsheet rows are horizontal and have numbers. There is no row C. The third row from the top is row 3. Columns are vertical and have letters. The third column from the left is column C. I cannot help further until you provide a meaningful description of your goal. Read section 0. Principal Components in Ten concepts that every Calc user should know. Is RowC a defined name?

You are more likely to get the problem solved quickly if you attach a spreadsheet document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the spreadsheet itself). I will not help further unless you attach a spreadsheet. My guess is that you need a recorded macro to:
   • copy data from a row or column, and then
   • execute Paste Special → Numbers+Text+Date&Time to store them in a different row or column
Those actions convert formulas to their values. The pasted values won't change until you run the macro again.
[Tutorial] Favorite Recorded Calc Macros

[Tutorial] Randomization in Calc → J. Gaining control of randomization using Paste Special discusses how to avoid a refresh of random numbers. Perhaps the same idea can be used with your spreadsheet.

A calculation column (ColumnZ) in the table is performed to compare each row in the table to data in RowC. As expected, if I then arrange or sort the table (ascending or descending) according to the ColumnZ the Z values change because the referenced RowR's contents change.

Many people do not understand what happens when ranges containing formulas are sorted. It is not what they expect.
[Tutorial] Formula Adjustments during Copy and Move
Sorting with formulas containing relative references
[Solved] Sort does not work with vertical cell-references
[Solved] Sorting on the results of formulas

[Cazer]

Hey there @heskencren,

Your problem is definitely an interesting one! To manually refresh a cell reference, one way could be to use a simple macro that can be assigned to a button. This macro would copy the desired row's data and paste it as values into your RowC. You could then sort or arrange your data without the values in RowC changing.

Here's a basic example of how the macro could look:

Code: Select all

Sub UpdateRowC
    Dim oSheet As Object
    Dim oRangeSource As Object
    Dim oRangeDestination As Object

    oSheet = ThisComponent.Sheets(0)
    oRangeSource = oSheet.getCellRangeByName("RowR") ' Adjust the range according to your needs
    oRangeDestination = oSheet.getCellRangeByName("RowC") ' Adjust the range according to your needs

    oRangeDestination.setDataArray(oRangeSource.getDataArray())
End Sub

You can assign this macro to a button that you can press whenever you want to update RowC. This should allow you to control when RowC is updated.

I hope this helps!

[heskencren]

Thank you all for your very thorough replies.
There's not much of a spreadsheet to upload really and it won't tell you much at this stage. Some of the content is also confidential.
Thank you for your basics checklist. I should have pointed out that I am not a novice user.
RowC isn't row C. RowC is a random name for a row in the calculations area of the spreadsheet. Just how I picture it in my head, sorry. It's not necessarily row 3 but it might be the 3rd one down in the calculations area/table/range or whatever you like to call it because there might be fixed data fields above all that that you might have to change occasionally. Like if there's a drift in declination or barometric pressure or something that affects data globally. It doesn't really matter anyway as the choice of row is rather arbitrary. It depends on which one I'm choosing to compare all the others with.

I suppose an analogy would be a list of club members' addresses and associated data for each address entry like skillsets, transport type offered, attributes, donations, hours service, attendance etc and so on. So you might want to pull up an address for, say, Charlie (RowC) and next to it you'll have the map coordinates for that so you can work out instantly who the next nearest person is using a basic Pythagorean thing. That would be equivalent to what I am trying to achieve I suppose if that helps. So each entry has a mixture of calculated stuff and trivia info if that makes sense.

So in this hypothetical case Charlie's row gets pasted up above somewhere in row 10 or wherever then the calcs reference that data that is now "fixed" data. Or instead of being pasted there's a reference down from there to whoever's button you press or whatever trigger is triggered.

I can't really describe it in any other way.

I hadn't found a function or command to interrupt the reference or update the reference so was just wondering if there was one hidden in a bit of the options list I hadn't found yet. The index search terms are a bit hit and miss sometimes.

So in the absence of any extant function it looks like a macro might be the best bet then?

Muchos

[heskencren]

The randomness of my descriptions and analogy have now assigned the name RowC to what was formerly called RowR.
So be careful if reading both descriptions of my requirement simultaneously!!!

[Cazer]

Once the macro I've talked about is set up, you can run it whenever you need to update 'RowC'. This gives you full control over when the reference updates, preventing automatic changes when you sort or arrange other parts of your table.
It may take a little bit of tweaking to get the macro working perfectly for your specific needs, but it should get you pretty close.

[heskencren]

Thank you Cazer.
I'll have a play with that over the weekend.
Muchos