Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and copy
Hello all,
I am trying to post a cell into sheet that has 1,000 lines to it. My problem is it must find 3 criteria's before posting. EX: sheet 1 is layed out like this A B C D date Job Mix 2/2/09 1 9.5 2/2/09 2 12.5 2/22/09 1 9.5 The information to be posted is found on sheet 2 A B C D date Job Mix Tons 2/2/09 2 12.5 200 <~~I will imput this information Now what I want is for Sheet 2 Column D to post on Sheet 1 Column D after it has found Columns A, B, & C from sheet 2. I hope this makes sense. Thank you for your help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and copy
You can use a formula in Sheet2 Column D
In D2 and copy down =INDEX(Sheet2!$D$2:$D$100,MATCH(1,(Sheet2!$A$2:$A$ 100=A3)*(Sheet2!$B$2:$B$100=B3)*(Sheet2!$C$2:$C$10 0=C3),0)) To handle the N/A# you can modify the formula as below =IF(ISNA(formula),"",formula) If this post helps click Yes --------------- Jacob Skaria "Eric" wrote: Hello all, I am trying to post a cell into sheet that has 1,000 lines to it. My problem is it must find 3 criteria's before posting. EX: sheet 1 is layed out like this A B C D date Job Mix 2/2/09 1 9.5 2/2/09 2 12.5 2/22/09 1 9.5 The information to be posted is found on sheet 2 A B C D date Job Mix Tons 2/2/09 2 12.5 200 <~~I will imput this information Now what I want is for Sheet 2 Column D to post on Sheet 1 Column D after it has found Columns A, B, & C from sheet 2. I hope this makes sense. Thank you for your help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and copy
If you are particular about a VBA solution try the below which run on Sheet1.
Sub Macro() Dim lngRow As Long For lngRow = 2 To Cells(Rows.Count, "A").End(xlUp).Row res = Evaluate("INDEX(Sheet2!$D$2:$D$100,MATCH(1, " & _ "(Sheet2!$A$2:$A$100=A" & lngRow & ")*(Sheet2!$B$2:$B$100=B" & _ lngRow & ")*(Sheet2!$C$2:$C$100=C" & lngRow & "),0))") If Not IsError(res) Then Range("D" & lngRow) = res Next End Sub If this post helps click Yes --------------- Jacob Skaria "Eric" wrote: Hello all, I am trying to post a cell into sheet that has 1,000 lines to it. My problem is it must find 3 criteria's before posting. EX: sheet 1 is layed out like this A B C D date Job Mix 2/2/09 1 9.5 2/2/09 2 12.5 2/22/09 1 9.5 The information to be posted is found on sheet 2 A B C D date Job Mix Tons 2/2/09 2 12.5 200 <~~I will imput this information Now what I want is for Sheet 2 Column D to post on Sheet 1 Column D after it has found Columns A, B, & C from sheet 2. I hope this makes sense. Thank you for your help. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and copy
Hello Jacob,
The formula isn't working and maybe it's because I am doing something wrong or didn't explain it well enough. I keep getting a #N/A error. I have tried different approaches but to no avail. The one row of information on sheet 2 is the only line that sheet 2 will have. So I am wanting to find the information in columns A, B, C on sheet 2 on sheet 1's columns A, B, C. When that is done I then want the information in column D from sheet 2 to be pasted in to sheet 1's column D in the same row where columns A, B, C, match sheet 2's columns A, B, & C Any more help would be of great appreciation. Thank you... Eric "Jacob Skaria" wrote: You can use a formula in Sheet2 Column D In D2 and copy down =INDEX(Sheet2!$D$2:$D$100,MATCH(1,(Sheet2!$A$2:$A$ 100=A3)*(Sheet2!$B$2:$B$100=B3)*(Sheet2!$C$2:$C$10 0=C3),0)) To handle the N/A# you can modify the formula as below =IF(ISNA(formula),"",formula) If this post helps click Yes --------------- Jacob Skaria "Eric" wrote: Hello all, I am trying to post a cell into sheet that has 1,000 lines to it. My problem is it must find 3 criteria's before posting. EX: sheet 1 is layed out like this A B C D date Job Mix 2/2/09 1 9.5 2/2/09 2 12.5 2/22/09 1 9.5 The information to be posted is found on sheet 2 A B C D date Job Mix Tons 2/2/09 2 12.5 200 <~~I will imput this information Now what I want is for Sheet 2 Column D to post on Sheet 1 Column D after it has found Columns A, B, & C from sheet 2. I hope this makes sense. Thank you for your help. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and copy
Hi Eric
Hope you have tried the macro I have missed to mention that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =INDEX(Sheet2!$D$2:$D$100,MATCH(1,(Sheet2!$A$2:$A$ 100=A3)*(Sheet2!$B$2:$B$100=B3)*(Sheet2!$C$2:$C$10 0=C3),0)) To handle NA# try the below..OR try the macro =IF(ISNA(INDEX(Sheet2!$D$2:$D$100,MATCH(1,(Sheet2! $A$2:$A$100=A3)*(Sheet2!$B$2:$B$100=B3)*(Sheet2!$C $2:$C$100=C3),0))),"",INDEX(Sheet2!$D$2:$D$100,MAT CH(1,(Sheet2!$A$2:$A$100=A3)*(Sheet2!$B$2:$B$100=B 3)*(Sheet2!$C$2:$C$100=C3),0))) -- If this post helps click Yes --------------- Jacob Skaria "Eric" wrote: Hello Jacob, The formula isn't working and maybe it's because I am doing something wrong or didn't explain it well enough. I keep getting a #N/A error. I have tried different approaches but to no avail. The one row of information on sheet 2 is the only line that sheet 2 will have. So I am wanting to find the information in columns A, B, C on sheet 2 on sheet 1's columns A, B, C. When that is done I then want the information in column D from sheet 2 to be pasted in to sheet 1's column D in the same row where columns A, B, C, match sheet 2's columns A, B, & C Any more help would be of great appreciation. Thank you... Eric "Jacob Skaria" wrote: You can use a formula in Sheet2 Column D In D2 and copy down =INDEX(Sheet2!$D$2:$D$100,MATCH(1,(Sheet2!$A$2:$A$ 100=A3)*(Sheet2!$B$2:$B$100=B3)*(Sheet2!$C$2:$C$10 0=C3),0)) To handle the N/A# you can modify the formula as below =IF(ISNA(formula),"",formula) If this post helps click Yes --------------- Jacob Skaria "Eric" wrote: Hello all, I am trying to post a cell into sheet that has 1,000 lines to it. My problem is it must find 3 criteria's before posting. EX: sheet 1 is layed out like this A B C D date Job Mix 2/2/09 1 9.5 2/2/09 2 12.5 2/22/09 1 9.5 The information to be posted is found on sheet 2 A B C D date Job Mix Tons 2/2/09 2 12.5 200 <~~I will imput this information Now what I want is for Sheet 2 Column D to post on Sheet 1 Column D after it has found Columns A, B, & C from sheet 2. I hope this makes sense. Thank you for your help. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and copy
Good Morning Jacob,
I was able to finaly figure out the Array. What I think I was doing incorrectly was using the index as a single cell not a group of cells. Anyway it works but it isn't keeping the number on sheet 1. Everytime I change the information on sheet 2 the previous find will go to #N/A. sheet 1 is layed out like this A B C D date Job Mix Tons 2/2/09 1 9.5 200 2/2/09 2 12.5 #N/A <~~ Found First and was correct 2/22/09 1 9.5 The information to be posted is found on sheet 2 A B C D date Job Mix Tons 2/2/09 1 9.5 200 <~~Second find Now if I go to another job the 200 in column D on sheet 1 will go to #N/A. Basicly I need the number to stay on sheet 1 in column D after is it found even when I am searching for another job, mix, or date. Eric "Jacob Skaria" wrote: Hi Eric Hope you have tried the macro I have missed to mention that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =INDEX(Sheet2!$D$2:$D$100,MATCH(1,(Sheet2!$A$2:$A$ 100=A3)*(Sheet2!$B$2:$B$100=B3)*(Sheet2!$C$2:$C$10 0=C3),0)) To handle NA# try the below..OR try the macro =IF(ISNA(INDEX(Sheet2!$D$2:$D$100,MATCH(1,(Sheet2! $A$2:$A$100=A3)*(Sheet2!$B$2:$B$100=B3)*(Sheet2!$C $2:$C$100=C3),0))),"",INDEX(Sheet2!$D$2:$D$100,MAT CH(1,(Sheet2!$A$2:$A$100=A3)*(Sheet2!$B$2:$B$100=B 3)*(Sheet2!$C$2:$C$100=C3),0))) -- If this post helps click Yes --------------- Jacob Skaria "Eric" wrote: Hello Jacob, The formula isn't working and maybe it's because I am doing something wrong or didn't explain it well enough. I keep getting a #N/A error. I have tried different approaches but to no avail. The one row of information on sheet 2 is the only line that sheet 2 will have. So I am wanting to find the information in columns A, B, C on sheet 2 on sheet 1's columns A, B, C. When that is done I then want the information in column D from sheet 2 to be pasted in to sheet 1's column D in the same row where columns A, B, C, match sheet 2's columns A, B, & C Any more help would be of great appreciation. Thank you... Eric "Jacob Skaria" wrote: You can use a formula in Sheet2 Column D In D2 and copy down =INDEX(Sheet2!$D$2:$D$100,MATCH(1,(Sheet2!$A$2:$A$ 100=A3)*(Sheet2!$B$2:$B$100=B3)*(Sheet2!$C$2:$C$10 0=C3),0)) To handle the N/A# you can modify the formula as below =IF(ISNA(formula),"",formula) If this post helps click Yes --------------- Jacob Skaria "Eric" wrote: Hello all, I am trying to post a cell into sheet that has 1,000 lines to it. My problem is it must find 3 criteria's before posting. EX: sheet 1 is layed out like this A B C D date Job Mix 2/2/09 1 9.5 2/2/09 2 12.5 2/22/09 1 9.5 The information to be posted is found on sheet 2 A B C D date Job Mix Tons 2/2/09 2 12.5 200 <~~I will imput this information Now what I want is for Sheet 2 Column D to post on Sheet 1 Column D after it has found Columns A, B, & C from sheet 2. I hope this makes sense. Thank you for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy contents of Find (Find and Replace) | Excel Discussion (Misc queries) | |||
Find in XML and Copy | Excel Discussion (Misc queries) | |||
Find and Copy | Excel Discussion (Misc queries) | |||
help on find and copy | Excel Programming | |||
Find and Copy | Excel Programming |