Re: [libreoffice-users] 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: 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