Re: [libreoffice-users] specify an absolute sheet address

Den lör 12 juni 2021 kl 11:06 skrev Thomas Blasejewicz <nyuwa@hb.tp1.jp>:

> Good evening
> I am probably, again!, too stupid to figure this out by myself,
> so I would be grateful for a hint.
>
> I have a file with something like 25 sheets listing the patients who
> visited my clinic before
> grouped by year. In one sheet I have all names (about 2,000).
>
> To make my work easier I set up a VLOOKUP function to help me enter zip
> codes.
> In sheet 2021 that formula looks like
> =VLOOKUP(N77,'Zip codes'.$A$2:$B$150,2,0)
> and refers to a sheet with the zip codes 3 sheets to the right. This
> works fine.
>
> However, when I copy the data (whenever there is something new) to the
> "ALL" sheet to the right of 2021,
> I get a "N/A", because the formula AGAIN refers to the THIRD sheet to
> the right, that does not have any zip codes.
>
> I tried to figure out, how to specify the sheet "Zip codes" as the
> absolute address,
> like:     =VLOOKUP(N76,$zip codes.$a$2$:$zip #REF!.$B$220,2,0)
> but so far all attempts (for quite a while now!) at placing those $
> marks have resulted in errors.

As long as you have a ”#REF!” involved you'll get an error. Get rid of that
first (replace it with what's supposed to be there), and then try again.
And what's ”$zip”? Is that a cell name? Then that's wrong too.

Another way to get around it is to name your range. Then your formula could
look something like =VLOOKUP(N76;ZipCodes;2;0)
Named ranges are absolute, always.


Kind regards

Johnny Rosenberg


> I am sure, this is something very simple, but being a simpleton I just
> cannot figure it out.
>
> Thank you.
> Thomas
>
> --
> 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

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