Home |
Search |
Today's Posts |
#1
|
|||
|
|||
look up a value that results in a cell address
I'm trying to compare two values in two different workbooks. Once compared i
need it to give me the cell address where it's located in workbook 2. Once i identify where the value is located, i need to go to that cell address to populate the cells adjacent to it. Can anyone help me figure out how to do this, please. Thank you. |
#2
|
|||
|
|||
Here's one interp / way ..
Assume this table is in Book1.xls, in Sheet1, cols A to C, data from row2 down Field1 Field2 Field3 Text1 Data1 Data11 Text2 Data2 Data12 Text3 Data3 Data13 Text4 Data4 Data14 Text5 Data5 Data15 etc (Text1, Text2, etc are assumed uniques in col A) Now, with Book1.xls open, assume we have this table in Book2.xls, in Sheet1 (say), cols A to C, data from row2 where we want to populate Fields 2 and 3 according to the items in col A extracting from the table in Book1.xls Field1 Field2 Field2 Text2 Text5 Text3 Text4 Text1 etc Put in B2: =VLOOKUP($A2,[Book1.xls]Sheet1!$A:$C,COLUMNS($A$1:B1),0) Copy across to C2, fill down to populate the table For the sample data above, we'll get: Field1 Field2 Field3 Text2 Data2 Data12 Text5 Data5 Data15 Text3 Data3 Data13 Text4 Data4 Data14 Text1 Data1 Data11 etc And perhaps better with an error trap included to return blanks: "" instead of #NAs for any unmatched items, we could put instead in B2: =IF(ISNA(MATCH($A2,[Book1.xls]Sheet1!$A:$A,0)),"",VLOOKUP($A2,[Book1.xls]She et1!$A:$C,COLUMNS($A$1:B1),0)) Copy across and down to populate the table -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "outlook help" wrote in message ... I'm trying to compare two values in two different workbooks. Once compared i need it to give me the cell address where it's located in workbook 2. Once i identify where the value is located, i need to go to that cell address to populate the cells adjacent to it. Can anyone help me figure out how to do this, please. Thank you. |
#3
|
|||
|
|||
Max,
thank you. i will give it a try. "Max" wrote: Here's one interp / way .. Assume this table is in Book1.xls, in Sheet1, cols A to C, data from row2 down Field1 Field2 Field3 Text1 Data1 Data11 Text2 Data2 Data12 Text3 Data3 Data13 Text4 Data4 Data14 Text5 Data5 Data15 etc (Text1, Text2, etc are assumed uniques in col A) Now, with Book1.xls open, assume we have this table in Book2.xls, in Sheet1 (say), cols A to C, data from row2 where we want to populate Fields 2 and 3 according to the items in col A extracting from the table in Book1.xls Field1 Field2 Field2 Text2 Text5 Text3 Text4 Text1 etc Put in B2: =VLOOKUP($A2,[Book1.xls]Sheet1!$A:$C,COLUMNS($A$1:B1),0) Copy across to C2, fill down to populate the table For the sample data above, we'll get: Field1 Field2 Field3 Text2 Data2 Data12 Text5 Data5 Data15 Text3 Data3 Data13 Text4 Data4 Data14 Text1 Data1 Data11 etc And perhaps better with an error trap included to return blanks: "" instead of #NAs for any unmatched items, we could put instead in B2: =IF(ISNA(MATCH($A2,[Book1.xls]Sheet1!$A:$A,0)),"",VLOOKUP($A2,[Book1.xls]She et1!$A:$C,COLUMNS($A$1:B1),0)) Copy across and down to populate the table -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "outlook help" wrote in message ... I'm trying to compare two values in two different workbooks. Once compared i need it to give me the cell address where it's located in workbook 2. Once i identify where the value is located, i need to go to that cell address to populate the cells adjacent to it. Can anyone help me figure out how to do this, please. Thank you. |
#4
|
|||
|
|||
You're welcome !
Thanks for the feedback -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- outlook help wrote in message ... Max, thank you. i will give it a try. |
#5
|
|||
|
|||
in the IF function is there anyway that i can have the 'if true' section of
the formula copy values of specific rows. In other words, =IF(ISNA(MATCH($A5,[SDS.xls]NOW!$L$3,0)), IF TRUE then, IF FAlSE then) then if true, copy cells n8:n12 and n14:n15 in workbook SDS to cell b5:g5 in workbook wkly mgt? Brigida "Max" wrote: You're welcome ! Thanks for the feedback -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- outlook help wrote in message ... Max, thank you. i will give it a try. |
#6
|
|||
|
|||
outlook help wrote
.... =IF(ISNA(MATCH($A5,[SDS.xls]NOW!$L$3,0)), IF TRUE then, IF FAlSE then) then if true, copy cells n8:n12 and n14:n15 in workbook SDS to cell b5:g5 in workbook wkly mgt? If I've read your intent correctly, Assuming the action if FALSE is to return blanks: "" and the output range is instead B5:H5 (think there's a typo in your "B5:G5") In book: wkly mgt ----------- Put in B5: =IF($A5<[SDS.xls]NOW!$L$3,INDIRECT("[SDS.xls]NOW!N"&COLUMNS($A$1:A1)+7),"") Copy B5 across to F5 The above will return cells n8:n12 in book: SDS into B5:F5, if TRUE Put in G5 =IF($A5<[SDS.xls]NOW!$L$3,INDIRECT("[SDS.xls]NOW!N"&COLUMNS($A$1:A1)+13),"" ) Copy G5 across to H5 The above will return cells n14:n15 in book: SDS into G5:H5, if TRUE -- There's no need to use: MATCH($A5,[SDS.xls]NOW!$L$3,0) if you're checking the match of a cell against another single cell range Note that INDIRECT requires the "slave" book, SDS.xls to be open, otherwise you'll get #REF! errors -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#7
|
|||
|
|||
Max,
This helped me tremendously. Is there any way i could send u an attachment to show you the links between the books as there is a 3rd one in the picture. "Max" wrote: outlook help wrote .... =IF(ISNA(MATCH($A5,[SDS.xls]NOW!$L$3,0)), IF TRUE then, IF FAlSE then) then if true, copy cells n8:n12 and n14:n15 in workbook SDS to cell b5:g5 in workbook wkly mgt? If I've read your intent correctly, Assuming the action if FALSE is to return blanks: "" and the output range is instead B5:H5 (think there's a typo in your "B5:G5") In book: wkly mgt ----------- Put in B5: =IF($A5<[SDS.xls]NOW!$L$3,INDIRECT("[SDS.xls]NOW!N"&COLUMNS($A$1:A1)+7),"") Copy B5 across to F5 The above will return cells n8:n12 in book: SDS into B5:F5, if TRUE Put in G5 =IF($A5<[SDS.xls]NOW!$L$3,INDIRECT("[SDS.xls]NOW!N"&COLUMNS($A$1:A1)+13),"" ) Copy G5 across to H5 The above will return cells n14:n15 in book: SDS into G5:H5, if TRUE -- There's no need to use: MATCH($A5,[SDS.xls]NOW!$L$3,0) if you're checking the match of a cell against another single cell range Note that INDIRECT requires the "slave" book, SDS.xls to be open, otherwise you'll get #REF! errors -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#8
|
|||
|
|||
Ok, you could send to either:
demechanik <atyahoo<dotcom or xdemechanik <atyahoo<dotcom -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "outlook help" wrote in message ... Max, This helped me tremendously. Is there any way i could send u an attachment to show you the links between the books as there is a 3rd one in the picture. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenate cells without specifying/writing cell address individually | Excel Discussion (Misc queries) | |||
How do I dynamically retrieve the cell address of the last cell t. | Excel Discussion (Misc queries) | |||
I want the results of a formula to show in cell, NOT THE FORMULA! | Excel Discussion (Misc queries) | |||
Using the results from two seperate cells to create cell reference | Excel Worksheet Functions | |||
can i colour a cell on basis of results of a formula e.g clour bl. | Excel Worksheet Functions |