Help with VLOOKUP across 2 sheets please.

classic Classic list List threaded Threaded
3 messages Options
Budge Budge
Reply | Threaded
Open this post in threaded view
|

Help with VLOOKUP across 2 sheets please.

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.

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

Grateful for any help when you have time please.

---
This email has been checked for viruses by AVG.
https://www.avg.com


--
To unsubscribe e-mail to: [hidden email]
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
Budge Budge
Reply | Threaded
Open this post in threaded view
|

Re: Help with VLOOKUP across 2 sheets please.

On 04/10/2018 10:24, 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.
>
> 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!!!
>
> Grateful for any help when you have time please.
>
> ---
> This email has been checked for viruses by AVG.
> https://www.avg.com
>

I think I have solved my automatic quotes repeat but still have the
VLOOKUP question please.

--
To unsubscribe e-mail to: [hidden email]
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-ml.mbourne libreoffice-ml.mbourne
Reply | Threaded
Open this post in threaded view
|

Re: Help with VLOOKUP across 2 sheets please.

In reply to this post by Budge
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: [hidden email]
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