Excel spreadsheet functions

[mnsebastian]

I am wondering if I could get some help or suggestions on how do do a
particular type of task.
The problem is that I don't know how to even google what I am trying
to ask because I am sure my question is not new.

I have several worksheets each has one common identifying number. But
the others have different information.

For example
Sheet1
a1=01234
b1=Fred
c1=Flinstone

Sheet2
A400=01234
b400=Ford
c400=Mustang

What I am trying to do is have Sheet1cell D1 search Sheet2 for the
corresponding value of sheet2 A:A and then populate Sheet1 D1 with the
value of Sheet2 B400

So the result will look as follows
Sheet1
a1=01234
b1=Fred
c1=Flinstone
D1=Ford

What is the proper term for what I am asking? function? script? It's
hard to search for answers to questions when you don't even know how
what you are asking.

Thanks for the time.

The other thing I have noticed is that usually when I paste the
information into a sheet (from a web page or import from csv) Excel
changes the value by dropping the leading 0

This is not a big problem but people are not used to looking at a
report and not seeing the leading 0 so I was wondering if there is a
way to get that the leading 0 is back in the columns.

Thanks again,
sebast

 

[Niek Otten]

You're looking for the VLOOKUP() function
Look here for a tutorial:

http://www.contextures.com/xlFunctions02.html

To retain the leading zero, for a fixed number of digits:
Format>Cells>Number tab, Custom, in the Type box, enter 0000 if you require 4 digits.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I am wondering if I could get some help or suggestions on how do do a
| particular type of task.
| The problem is that I don't know how to even google what I am trying
| to ask because I am sure my question is not new.
|
| I have several worksheets each has one common identifying number. But
| the others have different information.
|
| For example
| Sheet1
| a1=01234
| b1=Fred
| c1=Flinstone
|
| Sheet2
| A400=01234
| b400=Ford
| c400=Mustang
|
| What I am trying to do is have Sheet1cell D1 search Sheet2 for the
| corresponding value of sheet2 A:A and then populate Sheet1 D1 with the
| value of Sheet2 B400
|
| So the result will look as follows
| Sheet1
| a1=01234
| b1=Fred
| c1=Flinstone
| D1=Ford
|
| What is the proper term for what I am asking? function? script? It's
| hard to search for answers to questions when you don't even know how
| what you are asking.
|
| Thanks for the time.
|
| The other thing I have noticed is that usually when I paste the
| information into a sheet (from a web page or import from csv) Excel
| changes the value by dropping the leading 0
|
| This is not a big problem but people are not used to looking at a
| report and not seeing the leading 0 so I was wondering if there is a
| way to get that the leading 0 is back in the columns.
|
| Thanks again,
| sebast

 

[JLGWhiz]

If Niek's solution is not what you want, then try this:


Sub fillColD()
Dim c As Range, f As Range
lastRow = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
lstRw2 = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row
For Each c In Sheets(1).Range("A2:A" & lastRow)
Set f = Sheets(2).Range("A2:A" & lstRw2).Find(c.Value, LookIn:=xlValues,
LookAt:=xlWhole)
If Not f Is Nothing Then
f.Offset(0, 1).Copy Sheets(1).Range(c.Address).Offset(0, 3)
End If
Next
End Sub