LO 6.4.7.2 Calc: Where is a cell referenced elsewhere in s file?

users
#1

Hi,

I have a 134 column spreadsheet I am tidying up. An issue has arisen where if I delete a cell's content I do not know what other cell is referencing the deleted cell's content?

How can I determine this or should I raise a feature enhancement and if so where?

Regards
Hylton
I am a subscriber to the users list.

#2

I have a 134 column spreadsheet I am tidying up. An issue has arisen where if I delete a cell's content I do not know what other cell is referencing the deleted cell's content? How can I determine this ...

Use Tools | Detective > | Trace Dependents (or Shift+F5).

...or should I raise a feature enhancement and if so where?

Probably not.

I trust this helps.

Brian Barker

#3

Hi Hylton.
If you use find/replace you can search for a reference (i.e.B4) and search all sheets with the find next. Remember to search for B$4 also in case any formulae contain this reference format.
Steve

#4

Hi Hylton.
If you use find/replace you can search for a reference (i.e.B4) and
search all sheets with the find next. Remember to search for B$4 also in
case any formulae contain this reference format.
Steve

I immediately see two problems with that:

   - Let's say we have two sheets, Sheet1 and Sheet2. We are on Sheet1 and
   delete A1. Now we want to search for A1 in other cells in Sheet1. If we are
   searching for A1, A$1, $A1 and $A$1 we will also find Sheet2.A1, $Sheet2.A1
   and so on, which we probably don't want. You also probably want to search
   for A1 in Sheet2, but this time you don't want to find A1, $A1 and the
   others, since those are A1 on Sheet2
   - Maybe cell A1 is associated with a name, then you need to search for
   that too.

Maybe it can be done with regular expressions though, but how to find
Sheet1.A1 but not Sheet2.A1?
[^.]\${0,1}A\${0,1}1 finds A1, $A1, A$1 and $A$1 but unfortunately also
Sheet2.$A1. Seems to be hard to come around that problem, but maybe someone
here can do it.

Another way would be writing a macro for it.
Seems like Shift+F5 is supposed to be another way to do it. At least it
seems to work in my simple test sheet that I did for this question, but
when I tried it in a more complex one I didn't quite understand the result.
It seems to react to references on other sheets, but I don't understand
what to do with the information I'm given, a 45° rotated blue square in a
random (?) cell.

Kind regards

Johnny Rosenberg

#5

steve.edmonds@ptglobal.com>:

Hi Hylton.
If you use find/replace you can search for a reference (i.e.B4) and
search all sheets with the find next. Remember to search for B$4 also in
case any formulae contain this reference format.
Steve

I immediately see two problems with that:

    - Let's say we have two sheets, Sheet1 and Sheet2. We are on Sheet1 and
    delete A1. Now we want to search for A1 in other cells in Sheet1. If we are
    searching for A1, A$1, $A1 and $A$1 we will also find Sheet2.A1, $Sheet2.A1
    and so on, which we probably don't want. You also probably want to search
    for A1 in Sheet2, but this time you don't want to find A1, $A1 and the
    others, since those are A1 on Sheet2
    - Maybe cell A1 is associated with a name, then you need to search for
    that too.

Maybe it can be done with regular expressions though, but how to find
Sheet1.A1 but not Sheet2.A1?
[^.]\${0,1}A\${0,1}1 finds A1, $A1, A$1 and $A$1 but unfortunately also
Sheet2.$A1. Seems to be hard to come around that problem, but maybe someone
here can do it.

I replied off list with the REGEX \$?B\$?4 after testing it. Surprisingly this finds a match with or without sheet reference before the cell reference, but I do see the issue you raise regarding the need to consider the sheet number in the search term.
steve