Den lör 12 juni 2021 kl 11:06 skrev Thomas Blasejewicz <[email protected]>:
> 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: [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 > -- 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
