Remove Trace Arrows / Trace Arrow Toggle?

[makeitfolky]

I can find myway around a spreadsheet but am a basic user. I am just starting to use the Trace feature to see related cells. Seems straight forward enough: Shift - F5 to get the blue arrow.

But how do I remove it?? I was expecting Shift-f5 again in the same cell may toggle but doesn’t seem to, and the only solution I can find is Tools > Detective > Remove all Traces. But as I have about 100 rows I want to check, that seems quite a laborious approach. Any suggestions appreciated.

Alternatively - and I know this is crude, but would do the same for this purpose - Is there a function that will count the number of dependents a cell has (for example =count(dependents(B2)) ). If there is a similar for Precendents, that would also help.

(Basically I’m trying to untangle my tax affairs for the last five years where HMRC have applied payments to my account, seemingly at random and in a random order, and missed other payments off, so it’s a right old mess and a need something nice and visual so I can see what has and what hasn’t been paid!)

Thanks in advance for any thoughts

[gabix]

You can simply press Ctrl+Z to undo. Provided, of course, that you did nothing after enabling the arrows.

[makeitfolky]

Thanks, so there’s no way to toggle an arrow in a particular cell? (I currently have them switched on for 80 cells and now want to selectively turn a few off)

[erAck]

There’s no selective removal of traces, and also no function to count dependencies. Repeated Shift+F5 respectively Shift+F9 on a cell btw increase the level of precedents/dependents (precedents of precedents or dependents of dependents). So if you have =A1 in A2 and =A2 in A3, hitting Shift+F5 on A1 the first time adds an arrow between A1 and A2, hitting it a second time adds an arrow between A2 and A3.

[makeitfolky]

Thanks for the helpful answer.

[JohnSUN]

The Dependents/Precedents counting feature you mentioned is not very complicated. It could be something like this:

Function CountOfLinks(sAddr As String, PreOrDep As Boolean, recurs As Boolean) As Long 
Dim oCell As Object, oLinks As Variant, oEnum As Variant, i&
	On Error GoTo wrongAddr
	oCell = ThisComponent.getSheets().getCellRangesByName(sAddr)(0)
	If PreOrDep Then 
		oLinks = oCell.queryDependents(recurs)
	Else
		oLinks = oCell.queryPrecedents(recurs)
	EndIf 
	oEnum = oLinks.Cells.createEnumeration()
	i = 0
	While oEnum.hasMoreElements()
		If oEnum.nextElement().AbsoluteName<>oCell.AbsoluteName Then i=i+1
	Wend
	CountOfLinks = i
	Exit Function
wrongAddr:
	CountOfLinks = -1
End Function

And four generalized functions for simplified calling - just call CountOfLinks() function with different options

Function DEP(sAddr As String, Optional e As Variant) As Long 
	DEP = CountOfLinks(sAddr, True, False)
End Function

Function DEPALL(sAddr As String, Optional e As Variant) As Long 
	DEPALL = CountOfLinks(sAddr, True, True)
End Function

Function PRE(sAddr As String, Optional e As Variant) As Long 
	PRE = CountOfLinks(sAddr, False, False)
End Function

Function PREALL(sAddr As String, Optional e As Variant) As Long 
	PREALL = CountOfLinks(sAddr, False, True)
End Function

Calling functions can be so

By the way, you can set hotkey to commands Tools - Detective - Remove Precedents/Dependents

Hope this helps