Ask Your Question
0

To view ALL formula on Spreadsheet

asked 2016-10-22 19:45:14 +0200

beanbag gravatar image

Hi

New to LibreOffice, so please forgive the simple question !!

It's possible in Excel to view all formula on a sheet by doing :-

    1. In an empty cell, type = (equal sign).
    1. Click the Select All button (to the left of cell A1).
    1. Select the cell, and on the Formulas tab, in the Formula Auditing group, click Trace Precedents Button image twice.

I've managed to find the 'Tools' 'Detective' 'Trace Precedents', but can't work out how to display all formula on the sheet. I'm needing this to ensure the formulae in an XLS file is going to be stored in an ODS correctly

Many thanks

edit retag flag offensive close merge delete

4 Answers

Sort by » oldest newest most voted
0

answered 2016-10-22 21:37:28 +0200

Jens S gravatar image

Use shortcut CTRL + F10 to toggle view of formulas

edit flag offensive delete link more

Comments

Doesn't seem to work for me, selects File in menu and then I can navigate the menu with arrow keys. Same with pressing F10 or Alt key. Suspect this key combination might be used by system. Windows 8.1 with LibreOffice 5.2.2.2 64 bit.

mark_t gravatar imagemark_t ( 2016-10-22 22:01:45 +0200 )edit
0

answered 2016-10-22 20:41:55 +0200

mark_t gravatar image

updated 2016-10-23 21:59:20 +0200

Try setting the display formula option.

In the menu "Tools", "Options...", expand "LibreOffice Calc", select "View" and then under the Display settings enable "Formulae".

Edit: Adding macro which shows precedents of every cell in the range selected when the macro is executed. This is only a quick attempt and probably in need of some optimisation.

Do NOT select every cell of the worksheet as this will take a very long time as it includes all unused cells.

Sub TraceRangePrecedents()

    Dim oRange As Variant
    Dim oRangeAddress As Variant
    Dim oController As Variant
    Dim oDocument As Object
    Dim oDispatcher As Object
    Dim ic As Integer
    Dim ir As Long
    Dim sCell As String

    oRange = ThisComponent.CurrentSelection
    oRangeAddress = oRange.RangeAddress

    oController = ThisComponent.CurrentController
    oDocument   = oController.Frame
    oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

    For ic = oRangeAddress.StartColumn To oRangeAddress.EndColumn
        For ir = oRangeAddress.StartRow To oRangeAddress.EndRow 
            oController.select(oRange.Spreadsheet.getCellByPosition(ic, ir))

            oDispatcher.executeDispatch(oDocument, ".uno:ShowPrecedents", "", 0, Array())
        Next ir
    Next ic

    oController.select(oRange)
End Sub
edit flag offensive delete link more

Comments

Many thanks for this, but I was hoping to have the equivalent of Excel where there are blue lines going to the precedents, but this will certainly help me

beanbag gravatar imagebeanbag ( 2016-10-23 19:27:50 +0200 )edit
0

answered 2016-10-24 08:47:33 +0200

pierre-yves samyn gravatar image

Hi

I know this is not what you expect (display of blue lines auditing for all cells in one command) but a simple command might help you in diagnosing: ViewValue Highlighting (Ctrl+F8)

Text cells are formatted in black, number cells in blue, protected cells are shown with light grey background, no matter how their display is formatted, and formulas are formatted in green.

HTH

Regards

edit flag offensive delete link more
0

answered 2018-08-23 19:06:58 +0200

Loren gravatar image

In Libre Calc 5.4 there is a command View | Show Formula you can click and that will show all formulas. They aren't colored nicely, but they show up.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-10-22 19:45:14 +0200

Seen: 3,854 times

Last updated: Aug 23 '18