Importing data from another workbook

[TURK]

Hi,

If you enjoy a challange, you'll love this one !

I am trying to import data from another workbook, without much success I may add !
Below is an example of what I am trying to achieve.
Book1 is named 'Analysis', I want to import the data from column 5 in workbook 2 named 'reports'.

Book1.xls
	A	B	C	D
1	Jobno	Status	CNXcode	Date
2	11112222			02/01/2009
3	22223333			02/01/2009
4	44444444			02/01/2009
5	12345678			02/01/2009
6
7
8
analysis

Example of Book 2 'reports'
the result should be that the data of column E of book 2, would be imported into column B of book 1, when I type in column A the job number on book 1.

Book2.xls
	A	B	C	D	E	F
1	jobno	customer	region	data	status	date
2	22221111	allen	south		COM	02/01/2009
3	77770000	smith	south		COM	02/01/2009
4	11112222	jones	south		PAS	02/01/2009
5	12345678	kenny	south		COM	02/01/2009
6	44444444	audran	south		PAS	02/01/2009
7	11112222	hutch	south		PAS	02/01/2009
8
9
reports

The 'reports' are emailed to me on a regular basis, and will require updating, you will notice that the cell formats are different, unfortunately I have no control over this, does that matter ? because everything I have tried just hasn't worked. I kid you not, I have been at this for day's, reading all the threads on this forum, and trying various solutions to other peoples problems. But none of them seem to work for me.

thanks.

TURK.

 

[Jubjab]

Can't you just use a VLOOKUP to catch the status?

 

[BatleyBoy]

How do you want to update Book1? Cell by cell, or by checking all cells against Book2?

 

[TURK]

Hi,

When I tried VLOOKUP, the formula stayed in the cell, and i wasn't getting the result ! Don't know what i did wrong. But is VLOOKUP the best way to go ? or SUMPRODUCT ?
I got very confused as to which formula I was suppost to use, to get the correct result

TURK.

 

[TURK]

Hi BatleyBoy,

The job numbers on book1 get inputted one at a time, book2 already has the data on it.
the only data we can't input manually on a daily basis is the status. that comes from head office.

 

[BatleyBoy]

Hi TURK

Paste following code into Sheet1

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If ActiveCell.Column <> 1 Then
Exit Sub
ElseIf ActiveCell.Value = "" Then
Exit Sub
End If
With Workbooks("Book2").Sheets("Sheet1")
RowCounter = 2
While .Cells(RowCounter, 1) <> ""
If ActiveCell.Value = .Cells(RowCounter, 1).Value Then
ActiveCell.Offset(0, 1).Value = .Cells(RowCounter, 5).Value
Exit Sub
End If
RowCounter = RowCounter + 1
Wend
MsgBox "Value Not Found In Book2"
End With
End Sub

Whenever you right click in a cell, the status will be updated if data matches, or error message otherwise....

 

[TURK]

Hi Batleyboy,

Thanks for your help, very much appreciated !

But you'll have to excuse me for being a bit dim, is the code above VBA ?
I'm a 'Noobie' sorry but I am still trying to grasp the basics of excel, i won't even touch any sort of scrip or macro at this point, i'll leave for a later date, when I have grasped the concept of basic excel.

After a little more 'tinkering about with my formula, I finnally discovered why my formula was showing in the cell, as opposed to returning a result ..... the cells were formatted as TEXT ! <img class="inlineimg" src="images/icons/icon10.gif" alt="Talking" border="0">


TURK

 

[Billyboy]

Bro...easiest thing is the Vlookup function

here is what you do

Go to excel - click on the function button right above the worksheet - right above coloumn C called "fx"

Choose VLOOKUP function from there and click OK

In "Lookup_value", enter the cell# or click on the red arrow button on the side, go to the worksheet that has the primary search cell, click on the cell, click the red arrow again and voilaaa....u got it

In "Table_array", click the red arrow again, select the table range where you want to extract the information related to your search value from", click red box again and ur got it

in the "Col_index_num", its is basically the number of the column ur information is located in, starting from 1 - which is the column that contains your search value.

In "Range_lookup", enter FALSE is you want exact match or TRUE if you want approximate match of your search term. Only works well with numbers. not alphabets...

here is what the Vlookup should look like if you book2.xls is stored on the C drive......

=VLOOKUP(H10,'C:\[Book2.xls]Reports'!$A$2:$F$7,4,FALSE)

PEACE....!!!

 

[Billyboy]

instead of H10, the search column for you would be B10....