comparing two sheets og copy value

[Rockbear]

Hi
I have two sheets with about 500 rows
"Sheet1":
A1
3000
B1
3001
C1
3002
and furter on to about 500
"Sheet2":
A1
3002
B1
3000
C1
3001
and furter on to about 500

The function I need has to be put in cell D1 in "sheet1" and need to check
of any of the cells in column A in "sheet 2" has the value of A1 in "sheet1",
if it does I need to put the value of F1 in "sheet2" into D1 in "sheet1"

The cells in column A "sheet1" one has not the same rows as in "sheet2",, if
they had the same row it would be easy
Eks :
A1 in "sheet1" have the value 3000, but in "sheet2" the value 3000 is in A7


Do anyone understand what I am looking for???? PLS help

 

[Rockbear]

sorry got posted 2 times, got a errormessage the first time, and tried again,
sorry

 

[Sean Timmons]

=IF(ISNA(VLOOKUP(A1,Sheet2!A:A,1,0),"",F1)

If the value is not found, D1 will stay blank.

Copy down your column.

 

[Rockbear]

Did not work, sorry

=IF(ISNA(VLOOKUP(A4,'507.xls'!$A:$A,1,0),"",G4))

returned with #name?

 

[Sean Timmons]

Oops, I missed a )

=IF(ISNA(VLOOKUP(A4,'507.xls'!$A:$A,1,0)),"",G4)

 

[Rockbear]

I am still sorry it returns with #name?
and G4 is now H4, but it do not work

 

[Pete_UK]

The reason you are getting #NAME is because the formula is looking for
a sheet named 507.xls - not sure where this has come from !!

Try the formula like this:

=IF(ISNA(VLOOKUP(A4,Sheet2!$A:$A,1,0)),"",H4)

Hope this helps.

Pete

 

[Rockbear]

The formula should get the info from the file 507.xls ,sheet one
I have one big workbook(Totals) withs 25 sheets 500,505,507,510,511,513 ond
on to 585 totally 25 sheets
From our accounting comes daily a file for each sheet named 500.xls,
505.xls,507.xls
from these sheets I need the info in the H column into the "Totals" sheets,
but I need to check if the values in A collumn to se if I its the same value.
Eks:
Workbook file 500.xls, sheet one
A7 B7 C7 D7 to H7
3000 Sale 1990 2740 2997

Workbook Totals
A3 B3 C3 D3 to H3
3000 Sale 1990 2740 2997

I need the info from file 500.xls H7 into Totals.xls,sheet 500 cell I3
3000 is the account number, and its not in the same row in 500.xls all the
time, so i nee to check if its there in the file 500.xls, and if its there I
need the value of the H7 in this case.
The files in 500.xls is only a report of the accounts that have has any
movement that day
so one day it starts on account 3000, next on 3007
Hope i managed to explain it anyway thanks for helping

 

[Pete_UK]

The full syntax for the link will be:

'path[filename.xls]sheetname'!range_ref

Note the apostrophes and the square brackets. If the file is open at
the same time then you don't need to include the path.

Hope this helps.

Pete