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

classic Classic list List threaded Threaded
5 messages Options
zr1hpc zr1hpc
Reply | Threaded
Open this post in threaded view
|

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

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.

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

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

At 21:16 27/01/2021 +0200, Hylton Conacher wrote:
>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  


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

steveedmonds steveedmonds
Reply | Threaded
Open this post in threaded view
|

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

In reply to this post by zr1hpc
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

On 28/01/2021 08:16, Hylton Conacher (ZR1HPC) wrote:

> 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.
>


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Johnny Rosenberg Johnny Rosenberg
Reply | Threaded
Open this post in threaded view
|

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

Den tors 28 jan. 2021 kl 01:05 skrev Steve Edmonds <
[hidden email]>:

> 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

>
> On 28/01/2021 08:16, Hylton Conacher (ZR1HPC) wrote:
> > 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.
> >
>
>
> --
> To unsubscribe e-mail to: [hidden email]
> Problems?
> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy
>

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
steveedmonds steveedmonds
Reply | Threaded
Open this post in threaded view
|

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



On 29/01/2021 09:13, Johnny Rosenberg wrote:

> Den tors 28 jan. 2021 kl 01:05 skrev Steve Edmonds <
> [hidden email]>:
>
>> 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

>
> 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
>
>> On 28/01/2021 08:16, Hylton Conacher (ZR1HPC) wrote:
>>> 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.
>>>
>>
>> --
>> To unsubscribe e-mail to: [hidden email]
>> Problems?
>> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
>> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
>> List archive: https://listarchives.libreoffice.org/global/users/
>> Privacy Policy: https://www.documentfoundation.org/privacy
>>


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy