lookup values

S

SBSLIFER

On sheet 1, I have a list of names in one column (a) and rep #s in second
column (b). On sheet 2, I have list of names (in different order and some
may be on one list and not the other), and want to match up the rep# to
correct name from searching sheet 1.

So... if cell A1 in sheet 1 = any of the fields in sheet2 then determine
which row it's in and enter the data from the b column of that row.
 
F

Franz Verga

Nel post *SBSLIFER* ha scritto:
On sheet 1, I have a list of names in one column (a) and rep #s in
second column (b). On sheet 2, I have list of names (in different
order and some may be on one list and not the other), and want to
match up the rep# to correct name from searching sheet 1.

So... if cell A1 in sheet 1 = any of the fields in sheet2 then
determine which row it's in and enter the data from the b column of
that row.
Click to expand...

On sheet 2 I suppose you have names in column A, starting from A1, so put
this formula in B1 and copy down:

=VLOOKUP(A1;Sheet1!$A$1:$B$100;2;FALSE)

Adjust references as for your convenience.


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 
N

Norman Jones

Hi Frank,
=VLOOKUP(A1;Sheet1!$A$1:$B$100;2;FALSE)
Click to expand...

I think that you intended:

=VLOOKUP(A1,Sheet1!$A$1:$B$100,2,FALSE)

(semicolons replaced with commas)
 
F

Franz Verga

Nel post *Norman Jones* ha scritto:
Hi Frank,


I think that you intended:

=VLOOKUP(A1,Sheet1!$A$1:$B$100,2,FALSE)

(semicolons replaced with commas)
Click to expand...


Hi Norman,

You got it.

I wrote the formula directly in OE, so I wrote in the Italian way... ;-)
 
A

Ardus Petus

Hi Franz,

I often had the same problem.
You might find the following macro useful:

Sub CopyFormulaToClipBoard()
With New DataObject
.SetText ActiveCell.Formula
.PutInClipboard
End With
End Sub

HTH
 
F

Franz Verga

Nel post *Ardus Petus* ha scritto:
Hi Franz,
Click to expand...

Hi Ardus

I often had the same problem.
You might find the following macro useful:

Sub CopyFormulaToClipBoard()
With New DataObject
.SetText ActiveCell.Formula
.PutInClipboard
End With
End Sub
Click to expand...

I thank you, but usually when I write formulas in Excel, to translate them
from Italian into English, I use a very useful add-in TranslateIT (you can
find it http://members.chello.nl/jvolk/keepitcool/download.html), that
change also semicolon to commas. This time I typed the formula directly in
Outllok Express, so I wrote it in the Italian way... ;-)



--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 
A

Ardus Petus

Thanks for the tip.

I have not yet explored all the possibilities of that tool.

Cheers
 
N

Norman Jones

Hi Ardus,

Just to add that your suggestion requires that a reference be set in the
VBA project to the Microsoft Forms 2.0 object library.

That said, the code is very useful and, as Chip Pearson suggests, I have
added similar code to my right-click context menu.
 
Top