[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

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

[Thread Prev] | [Thread Next]

*Subject*: Re: [libreoffice-users] Calc: moving/sorting rows on Sheet1 don't update formulas on Sheet2 that refer to them*From*: Remy Gauthier <remygauthier@yahoo.com>*Date*: Wed, 10 Apr 2019 20:04:04 -0400*To*: users@global.libreoffice.org

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

[libreoffice-users] Calc: moving/sorting rows on Sheet1 don't update formulas on Sheet2 that refer to them | Tim Deaton <tim@timdeaton.org> |

Re: [libreoffice-users] Calc: moving/sorting rows on Sheet1 don't update formulas on Sheet2 that refer to them | Steve Edmonds <steve.edmonds@ptglobal.com> |

Re: [libreoffice-users] Calc: moving/sorting rows on Sheet1 don't update formulas on Sheet2 that refer to them | Tim Deaton <tim@timdeaton.org> |

Luuk <luuk34@gmail.com> |

- Prev by Date:
**Re: [libreoffice-users] Page number messed up in LO Writer 6.2.1/2 and 6.1.5** - Next by Date:
**[libreoffice-users] Manually installed LibreOffice reports a wrong version number** - Previous by thread:
- Next by thread:
**[libreoffice-users] Page number messed up in LO Writer 6.2.1/2 and 6.1.5** - Index(es):

Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.