Re: [libreoffice-users] Calc: moving/sorting rows on Sheet1 don't update formulas on Sheet2 that refer to them

At 19:48 09/04/2019 -0400, Tim Deaton wrote:
> I'm building a spreadsheet to serve as a food diary. On Sheet1 I 
> have a list of different foods, and on Sheet2 I have what foods I 
> ate at which day and meal. So far, I've only got a couple of days' 
> data posted. As I add more meals, I eat items that are not already 
> on the Sheet1 list. So I add the new items to Sheet1, and sort that 
> table again.

Why do you sort it? Does lasagne really need to come before sausages? 
Is that how you order them in your pantry?

> THAT's when the problem occurs. I just added three new foods to the 
> bottom of the Sheet1 list, then sorted it. Before sorting, the new 
> foods were in cells A38-A40. After sorting, the new entries are all above A30.
>
> Before sorting Sheet1, the item in Sheet2, cell C5 contained this:
> =$'Sheet1'.A33
> After sorting Sheet 1, that Sheet2 cell reference should say
> =$'Sheet1'.A36, but it still says "A33".

Whatever makes you think that your formulae "should" change?

> Is this a known problem?

Probably yes - but not with the program. If you really need to sort 
your original table, you probably need VLOOKUP() in your formulae to 
locate the appropriate data.

I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
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