look up a value that results in a cell address

[Guest]

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.

 

[Max]

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

 

[Guest]

Max,

thank you. i will give it a try.

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 <at>yahoo<dot>com
----

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.

 

[Max]

You're welcome !
Thanks for the feedback

 

[Guest]

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]

=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

 

[Guest]

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.

=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

 

[Max]

Ok, you could send to either:

demechanik <at>yahoo<dot>com

or

xdemechanik <at>yahoo<dot>com
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

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.