Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
outlook help
 
Posts: n/a
Default 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   Report Post  
Max
 
Posts: n/a
Default

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   Report Post  
outlook help
 
Posts: n/a
Default

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   Report Post  
Max
 
Posts: n/a
Default

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   Report Post  
outlook help
 
Posts: n/a
Default

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   Report Post  
Max
 
Posts: n/a
Default

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   Report Post  
outlook help
 
Posts: n/a
Default

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   Report Post  
Max
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Concatenate cells without specifying/writing cell address individually Hari Excel Discussion (Misc queries) 4 January 3rd 05 07:05 PM
How do I dynamically retrieve the cell address of the last cell t. Nancy Excel Discussion (Misc queries) 1 December 20th 04 03:52 PM
I want the results of a formula to show in cell, NOT THE FORMULA! ocbecky Excel Discussion (Misc queries) 4 December 10th 04 09:39 PM
Using the results from two seperate cells to create cell reference DarrenWood Excel Worksheet Functions 2 November 14th 04 11:35 PM
can i colour a cell on basis of results of a formula e.g clour bl. K Excel Worksheet Functions 3 November 4th 04 07:18 PM


All times are GMT +1. The time now is 10:53 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"