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: [email protected]
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
