Calc: moving/sorting rows on Sheet1 don't update formulas on Sheet2 that refer to them

classic Classic list List threaded Threaded
6 messages Options
Tim Deaton Tim Deaton
Reply | Threaded
Open this post in threaded view
|

Calc: moving/sorting rows on Sheet1 don't update formulas on Sheet2 that refer to them

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

Is this a known problem?  I'm using LO v6.1.5.2 (x64) on Windows 7.

--
-- Tim Deaton
===========================
I know the plans I have for you:
Plans to prosper you and not to harm you;
Plans to give you hope and a future.
                --- God (Jeremiah 29:11)


--
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: 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: [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: Calc: moving/sorting rows on Sheet1 don't update formulas on Sheet2 that refer to them

In reply to this post by Tim Deaton
I get the same on 6.07, it is what I would have expected from a sort
(the contents of the cells are moved about, the cell positions are not
changed). Seems logical to me but possibly not the desired outcome for
others.
If you use a lookup function on sheet 2 (include an index for each row
for lookup ease) then you will return the right results regardless of
position in the lookup table. You just have to be careful adding extra
lines that they get included in the table.

On 10/04/2019 11:48, 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. 
> 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".
>
> Is this a known problem?  I'm using LO v6.1.5.2 (x64) on Windows 7.
>


--
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
Tim Deaton Tim Deaton
Reply | Threaded
Open this post in threaded view
|

Re: Calc: moving/sorting rows on Sheet1 don't update formulas on Sheet2 that refer to them

Steve & Brian, thanks for your replies.

Despite decades of working on spreadsheets, I can't say I expected it. 
But then, it's the first time I'd found myself in this particular
scenario.  But after your first sentence, I think I can see the logic -
even though I wish the spreadsheet was smarter.

I actually am doing a lookup from Sheet2.  But the food names on Sheet1
is the index column, and I'm using the lookup function to pull the
calories, carbs, etc into Sheet2.  That's why I was sorting Sheet1 on
the food names - both to make it easier for VLOOKUP to find a match, and
to make it easier for me to create the link between the two sheets.

Unless someone has any other suggestions, I guess I'll have to remember
to insert new rows wherever my noew foods need to be, and/or to use
ALT+SHIFT to drag new rows to where I should have inserted them before I
created them.  I'll probably also need to regularly use |Paste Special|
to convert those formulas pulling the food names into Sheet2 into hard
text, since this is more fragile than I expected it to be.

Perhaps there's a way to setup a pull-down pick-list to pull the food
names into Sheet2?  Perhaps I can investigate that later.

-- Tim
===========================

On 4/9/2019 8:58 PM, Steve Edmonds wrote:

> I get the same on 6.07, it is what I would have expected from a sort
> (the contents of the cells are moved about, the cell positions are not
> changed). Seems logical to me but possibly not the desired outcome for
> others.
> If you use a lookup function on sheet 2 (include an index for each row
> for lookup ease) then you will return the right results regardless of
> position in the lookup table. You just have to be careful adding extra
> lines that they get included in the table.
>
> On 10/04/2019 11:48, 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.
>> 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".
>>
>> Is this a known problem?  I'm using LO v6.1.5.2 (x64) on Windows 7.
>>
>

--
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
Luuk Luuk
Reply | Threaded
Open this post in threaded view
|

Re: Calc: moving/sorting rows on Sheet1 don't update formulas on Sheet2 that refer to them


On 10-4-2019 05:32, Tim Deaton wrote:

> Steve & Brian, thanks for your replies.
>
> Despite decades of working on spreadsheets, I can't say I expected
> it.  But then, it's the first time I'd found myself in this particular
> scenario.  But after your first sentence, I think I can see the logic
> - even though I wish the spreadsheet was smarter.
>
> I actually am doing a lookup from Sheet2.  But the food names on
> Sheet1 is the index column, and I'm using the lookup function to pull
> the calories, carbs, etc into Sheet2.  That's why I was sorting Sheet1
> on the food names - both to make it easier for VLOOKUP to find a
> match, and to make it easier for me to create the link between the two
> sheets.
>
> Unless someone has any other suggestions, I guess I'll have to
> remember to insert new rows wherever my noew foods need to be, and/or
> to use ALT+SHIFT to drag new rows to where I should have inserted them
> before I created them.  I'll probably also need to regularly use
> |Paste Special| to convert those formulas pulling the food names into
> Sheet2 into hard text, since this is more fragile than I expected it
> to be.
>
> Perhaps there's a way to setup a pull-down pick-list to pull the food
> names into Sheet2?  Perhaps I can investigate that later.
>
> -- Tim
>
You should not be doing a lookup from sheet2, but you should do it from
sheet 1

Simple Example:

Sheet1:

Three columns

A Date; i.e. 10-04-2019   ('DD-MM-YYYY', or whatever dateformat you are
using)

B Food; ie. 'pasta', 'pizza'

C Carbohydrates; formula:
=IF(ISERR(VLOOKUP(B2;Food.$A$1:$B$50;2;0));"?";
VLOOKUP(B2;Food.$A$1:$B$50;2;0))


Sheet2:

Two columns

A Food; i.e. 'pasta','pizza','macaroni', etc....

B Carbohydrates;  the carbohydrates in the food...


After this sorting sheet1, or sheet2 is no problem.

The only 'problem' i have with spreadsheets is that i am always
referring to fixed ranges (like the $A$1:$B$50), which fail after adding
more than 50 lines.... ;)







40




--
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
Windows10 / LibreOffice  (latest?)
remygauthier remygauthier
Reply | Threaded
Open this post in threaded view
|

Re: Calc: moving/sorting rows on Sheet1 don't update formulas on Sheet2 that refer to them

Hi,

There are two ways to fix your problem:

1) Insert cells to place the new entries at the top, above cells A2-B2
, which will move down the reference of the last cell automatically.
The new cells must be in the range of the search array (if you insert
above A1, the search array reference will be completely moved down and
you will not get the expected effect). Then sort.
2) Use the OFFSET function along with the COUNTIF function, something
like this:

=VLOOKUP(B2,OFFSET(Food.$A$1,0,0,COUNTIF(Food.$A:$A,"<>"""""),2),2,0)

What this does:

COUNTIF(Food.$A:$A,"<>""""") counts the number of non-empty cells in
the first column of the Food sheet. This evaluation uses the full
height of the worksheet
OFFSET(Food.$A$1,0,0,COUNTIF(Food.$A:$A,"<>"""""),2) creates the
reference similar to Food.$A$1:$B$50, but the height is the number of
non-empty cells given by countif, with a fixed with of 2, anchored at
cell Food.$a$1. This is done dynamically, as you add entries in your
Food columns.

To use this, simply place the entries at the end of the existing list,
and sort.

Then VLOOKUP then just does the search. Also, since you are using 0 as
last argument to VLOOKUP, your list does not need to be sorted since
VLOOKUP will look for an exact match wherever the match will be in the
search array.

And you can also simplify your formula like this:

=IFERROR(VLOOKUP(B2,OFFSET(Food.$A$1,0,0,COUNTIF(Food.$A:$A,"<>"""""),2
),2,0),"?")

IFERROR will present the result of the first argument or if there is
any error, the second argument, which can also be any other set of
functions.

I hope this helps.

Rémy.


Le mercredi 10 avril 2019 à 09:58 +0200, Luuk a écrit :

> On 10-4-2019 05:32, Tim Deaton wrote:
>
> Steve & Brian, thanks for your replies.
>
> Despite decades of working on spreadsheets, I can't say I expected
> it.  But then, it's the first time I'd found myself in this particular
> scenario.  But after your first sentence, I think I can see the logic
> - even though I wish the spreadsheet was smarter.
>
> I actually am doing a lookup from Sheet2.  But the food names on
> Sheet1 is the index column, and I'm using the lookup function to pull
> the calories, carbs, etc into Sheet2.  That's why I was sorting Sheet1
> on the food names - both to make it easier for VLOOKUP to find a
> match, and to make it easier for me to create the link between the two
> sheets.
>
> Unless someone has any other suggestions, I guess I'll have to
> remember to insert new rows wherever my noew foods need to be, and/or
> to use ALT+SHIFT to drag new rows to where I should have inserted them
> before I created them.  I'll probably also need to regularly use
>
> Paste Special| to convert those formulas pulling the food names into
> Sheet2 into hard text, since this is more fragile than I expected it
> to be.
>
> Perhaps there's a way to setup a pull-down pick-list to pull the food
> names into Sheet2?  Perhaps I can investigate that later.
>
> -- Tim
>
> You should not be doing a lookup from sheet2, but you should do it from
> sheet 1
>
> Simple Example:
>
> Sheet1:
>
> Three columns
>
> A Date; i.e. 10-04-2019   ('DD-MM-YYYY', or whatever dateformat you are
> using)
>
> B Food; ie. 'pasta', 'pizza'
>
> C Carbohydrates; formula:
> =IF(ISERR(VLOOKUP(B2;Food.$A$1:$B$50;2;0));"?";
> VLOOKUP(B2;Food.$A$1:$B$50;2;0))
>
>
> Sheet2:
>
> Two columns
>
> A Food; i.e. 'pasta','pizza','macaroni', etc....
>
> B Carbohydrates;  the carbohydrates in the food...
>
>
> After this sorting sheet1, or sheet2 is no problem.
>
> The only 'problem' i have with spreadsheets is that i am always
> referring to fixed ranges (like the $A$1:$B$50), which fail after adding
> more than 50 lines.... ;)
>
>
>
>
>
>
>
> 40
>
>
>
>
>

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