Re: [libreoffice-users] Help with VLOOKUP across 2 sheets please.

Budge wrote:
> I cannot sort out my vlookup when looking up in another sheet and 
> entering in first sheet. I was hoping to ask for help but now I can't 
> even highlight my entry and right click to copy and paste here.  As soon 
> as I highlight line it goes black and will not right click.

Just tried, and I see what you mean. Having selected text directly in 
the cell, right-click doesn't do anything.

I hadn't noticed that before. Maybe it's a new change (I updated to 
LibreOffice 6.1.2 on Linux Mint yesterday), or I just haven't noticed 
since I generally use the keyboard shortcuts anyway.

A few other ways of copying the formula which still work:
- From the formula bar, just under the toolbar, you can select, 
right-click and copy
- From directly in the cell, select and use the menu Edit > Copy, or 
press Ctrl+C; these still work, even though you can't right-click

> Also I cannot get single quote to work even though as you advised a 
> while ago I have Tools Autocorrect Options > Localized Options turned off
> Am I losing my grip?
>
> I shall type in what I have now:-
>
> =VLOOKUP(A2,$Sheet1.$A$2:$B$1276,2,0)
>
> I am seeking to find the value in A2 in first sheet from an array in 
> "sheet1" (the second sheet) column A and enter the result in the entry 
> column in the first sheet.  I think there should be single quotes around 
> Sheet1 which is the label of sheet 2 but I cannot get them to save!!!

The formula you show seems to work for me, assuming your "second sheet" 
is (somewhat confusingly) named "Sheet1". LibreOffice 6.1.2 on Linux 
Mint, but I don't think the version of OS should make any difference in 
this case.

I put the following data starting at A2 in Sheet1 (your "second sheet"):
  q     a
  w     b
  e     c
  r     d
  t     e
  y     f
  u     g
  i     h
  o     I
  p     m

Your formula in cell B2 of Sheet2 (corresponding to your unnamed "first 
sheet"):
  =VLOOKUP(A2,$Sheet1.$A$2:$B$1276,2,0)
If I put one of the letters q, w, e, r, t, y, u, i, o, p in cell A2 of 
that sheet, the formula in B2 shows the corresponding letter from column 
B of Sheet1.

Perhaps I've misunderstood what you're trying to do, or maybe there's 
something about the specific data you're using which causes this to 
misbehave. There has to be an exact match between the value looked up 
and the value in Sheet1 column A. Make sure there are no leading or 
trailing spaces in any of the values, for example.

Quotes are only needed around the sheet name if there are spaces in the 
name.

--
Mark.


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