| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
comparing two sheets og copy value
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 :) -- Just a regular user -- Just a regular user |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
comparing two sheets og copy value
sorry got posted 2 times, got a errormessage the first time, and tried again,
sorry -- Just a regular user Rockbear skrev: 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 :) -- Just a regular user -- Just a regular user |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
comparing two sheets og copy value
=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" wrote: sorry got posted 2 times, got a errormessage the first time, and tried again, sorry -- Just a regular user Rockbear skrev: 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 :) -- Just a regular user -- Just a regular user |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
comparing two sheets og copy value
Did not work, sorry
=IF(ISNA(VLOOKUP(A4,'507.xls'!$A:$A,1,0),"",G4)) returned with #name? -- Just a regular user Sean Timmons skrev: =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" wrote: sorry got posted 2 times, got a errormessage the first time, and tried again, sorry -- Just a regular user Rockbear skrev: 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 :) -- Just a regular user -- Just a regular user |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
comparing two sheets og copy value
Oops, I missed a )
=IF(ISNA(VLOOKUP(A4,'507.xls'!$A:$A,1,0)),"",G4) "Rockbear" wrote: Did not work, sorry =IF(ISNA(VLOOKUP(A4,'507.xls'!$A:$A,1,0),"",G4)) returned with #name? -- Just a regular user Sean Timmons skrev: =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" wrote: sorry got posted 2 times, got a errormessage the first time, and tried again, sorry -- Just a regular user Rockbear skrev: 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 :) -- Just a regular user -- Just a regular user |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
comparing two sheets og copy value
I am still sorry it returns with #name?
and G4 is now H4, but it do not work -- Just a regular user "Sean Timmons" wrote: Oops, I missed a ) =IF(ISNA(VLOOKUP(A4,'507.xls'!$A:$A,1,0)),"",G4) "Rockbear" wrote: Did not work, sorry =IF(ISNA(VLOOKUP(A4,'507.xls'!$A:$A,1,0),"",G4)) returned with #name? -- Just a regular user Sean Timmons skrev: =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" wrote: sorry got posted 2 times, got a errormessage the first time, and tried again, sorry -- Just a regular user Rockbear skrev: 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 :) -- Just a regular user -- Just a regular user |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
comparing two sheets og copy value
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 On Oct 14, 9:50*am, Rockbear wrote: I am still sorry it returns with #name? and G4 is now H4, but it do not work -- Just a regular user "Sean Timmons" wrote: Oops, I missed a ) =IF(ISNA(VLOOKUP(A4,'507.xls'!$A:$A,1,0)),"",G4) "Rockbear" wrote: Did not work, sorry =IF(ISNA(VLOOKUP(A4,'507.xls'!$A:$A,1,0),"",G4)) returned with #name? -- Just a regular user Sean Timmons skrev: =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" wrote: sorry got posted 2 times, got a errormessage the first time, and tried again, sorry -- Just a regular user Rockbear skrev: 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 :) -- Just a regular user -- Just a regular user- Hide quoted text - - Show quoted text - |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
comparing two sheets og copy value
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 -- Just a regular user "Pete_UK" wrote: 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 On Oct 14, 9:50 am, Rockbear wrote: I am still sorry it returns with #name? and G4 is now H4, but it do not work -- Just a regular user "Sean Timmons" wrote: Oops, I missed a ) =IF(ISNA(VLOOKUP(A4,'507.xls'!$A:$A,1,0)),"",G4) "Rockbear" wrote: Did not work, sorry =IF(ISNA(VLOOKUP(A4,'507.xls'!$A:$A,1,0),"",G4)) returned with #name? -- Just a regular user Sean Timmons skrev: =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" wrote: sorry got posted 2 times, got a errormessage the first time, and tried again, sorry -- Just a regular user Rockbear skrev: 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 :) -- Just a regular user -- Just a regular user- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
comparing two sheets og copy value
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 On Oct 14, 2:50*pm, Rockbear wrote: 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 -- Just a regular user "Pete_UK" wrote: 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 On Oct 14, 9:50 am, Rockbear wrote: I am still sorry it returns with #name? and G4 is now H4, but it do not work -- Just a regular user "Sean Timmons" wrote: Oops, I missed a ) =IF(ISNA(VLOOKUP(A4,'507.xls'!$A:$A,1,0)),"",G4) "Rockbear" wrote: Did not work, sorry =IF(ISNA(VLOOKUP(A4,'507.xls'!$A:$A,1,0),"",G4)) returned with #name? -- Just a regular user Sean Timmons skrev: =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" wrote: sorry got posted 2 times, got a errormessage the first time, and tried again, sorry -- Just a regular user Rockbear skrev: 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 :) -- Just a regular user -- Just a regular user- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Comparing Two Sheets | Excel Worksheet Functions | |||
| Comparing 2 sheets | Excel Discussion (Misc queries) | |||
| comparing 2 sheets | Excel Discussion (Misc queries) | |||
| comparing 2 sheets | Excel Worksheet Functions | |||
| in VBA Sheets("mysheet").Copy Befo=Sheets(1) how do i get a reference to the newly created copy of this sheet? | Excel Worksheet Functions | |||
Linear Mode
