Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 3rd 06, 12:17 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 7
Default Simple match and link

I've been reading for the last hour or so thru the group and have tried
to modify some of the formulas I thought might help me, but I'm just
not getting it.

Sheet1 contains names, addresses, reference numbers (Column M), etc.
Sheet2 contains codes (Column A) and reference numbers (Column H).

I want to have a column in Sheet1 that matches up the reference numbers
and then either links to the exact cell in Sheet2 OR pulls in Sheet2's
code.

One difficulty might be that the reference numbers (Column M) in Sheet1
also contain various other info/numbers. For example:

In Spread1 (Column M140), we have: "8489498 2/07 54978 - link 3"
whereas, Spread2 (Column H3380) would have ONLY the matching number:
"8489498."

I hope I've made this clear. Not exactly sure what questions to ask
so, if you can help, I'll answer any question asap.


  #2   Report Post  
Old July 3rd 06, 01:35 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,688
Default Simple match and link

Hi!

8489498 2/07 54978 - link 3
8489498

Are the reference numbers all the same format? 7 digits long followed by a
space and then other characters? Do any of the reference numbers have
leading zeros? Are these reference numbers TEXT?

Excel see's this as a TEXT string: 8489498 2/07 54978 - link 3
And see's this as a NUMBER: 8489498

You can probably start with something like this:

=INDEX(Sheet2!A2:A10,MATCH(--LEFT(M2,7),Sheet2!H2:H10,0))

Biff

"motol" wrote in message
ups.com...
I've been reading for the last hour or so thru the group and have tried
to modify some of the formulas I thought might help me, but I'm just
not getting it.

Sheet1 contains names, addresses, reference numbers (Column M), etc.
Sheet2 contains codes (Column A) and reference numbers (Column H).

I want to have a column in Sheet1 that matches up the reference numbers
and then either links to the exact cell in Sheet2 OR pulls in Sheet2's
code.

One difficulty might be that the reference numbers (Column M) in Sheet1
also contain various other info/numbers. For example:

In Spread1 (Column M140), we have: "8489498 2/07 54978 - link 3"
whereas, Spread2 (Column H3380) would have ONLY the matching number:
"8489498."

I hope I've made this clear. Not exactly sure what questions to ask
so, if you can help, I'll answer any question asap.



  #3   Report Post  
Old July 3rd 06, 11:30 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22
Default Simple match and link

Biff:

Might you need a "value" function for the "left" function, to derive the
numeric value to accurately compare?

Also, I've spent some time reading through these messages. I notice on a
some of your posts you put "--" [such as "(--LEFT(M2,7)..."]. What does "--"
mean? Thanks.

"Biff" wrote:

Hi!

8489498 2/07 54978 - link 3
8489498

Are the reference numbers all the same format? 7 digits long followed by a
space and then other characters? Do any of the reference numbers have
leading zeros? Are these reference numbers TEXT?

Excel see's this as a TEXT string: 8489498 2/07 54978 - link 3
And see's this as a NUMBER: 8489498

You can probably start with something like this:

=INDEX(Sheet2!A2:A10,MATCH(--LEFT(M2,7),Sheet2!H2:H10,0))

Biff

"motol" wrote in message
ups.com...
I've been reading for the last hour or so thru the group and have tried
to modify some of the formulas I thought might help me, but I'm just
not getting it.

Sheet1 contains names, addresses, reference numbers (Column M), etc.
Sheet2 contains codes (Column A) and reference numbers (Column H).

I want to have a column in Sheet1 that matches up the reference numbers
and then either links to the exact cell in Sheet2 OR pulls in Sheet2's
code.

One difficulty might be that the reference numbers (Column M) in Sheet1
also contain various other info/numbers. For example:

In Spread1 (Column M140), we have: "8489498 2/07 54978 - link 3"
whereas, Spread2 (Column H3380) would have ONLY the matching number:
"8489498."

I hope I've made this clear. Not exactly sure what questions to ask
so, if you can help, I'll answer any question asap.




  #4   Report Post  
Old July 4th 06, 02:21 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,688
Default Simple match and link

"bman342" wrote in message
...
Biff:

Might you need a "value" function for the "left" function, to derive the
numeric value to accurately compare?

Also, I've spent some time reading through these messages. I notice on a
some of your posts you put "--" [such as "(--LEFT(M2,7)..."]. What does
"--"
mean? Thanks.


That's what the double unary does, it converts a TEXT number into a NUMERIC
number.

Since the LEFT function returns a TEXT value the "--" comverts the result to
a NUMERIC number. You can do the same thing using any of these methods:

VALUE(LEFT(M2,7))
1*LEFT(M2,7)
0+LEFT(M2,7)
--LEFT(M2,7)

I just use the double unary as personal preference.

Biff


  #5   Report Post  
Old July 4th 06, 03:06 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 7
Default Simple match and link

Thanks for the reply. I managed to figure it out! Thought I'd post it
in case it might help someone else. Or, if anyone sees a way I can
clean it up a bit if it's redundant...

=INDEX(codes!$A$2:$B$4001,MATCH($M3491,codes!$A$2: $A$4001,0),2)




Biff wrote:
"bman342" wrote in message
...
Biff:

Might you need a "value" function for the "left" function, to derive the
numeric value to accurately compare?

Also, I've spent some time reading through these messages. I notice on a
some of your posts you put "--" [such as "(--LEFT(M2,7)..."]. What does
"--"
mean? Thanks.


That's what the double unary does, it converts a TEXT number into a NUMERIC
number.

Since the LEFT function returns a TEXT value the "--" comverts the result to
a NUMERIC number. You can do the same thing using any of these methods:

VALUE(LEFT(M2,7))
1*LEFT(M2,7)
0+LEFT(M2,7)
--LEFT(M2,7)

I just use the double unary as personal preference.

Biff




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
Double and Multiple Lookup Using the MATCH Function Charles793 Excel Worksheet Functions 0 May 11th 06 01:46 PM
Formula to Link to Specific List on Another Worksheet jdurrmsu Excel Worksheet Functions 1 February 5th 06 06:23 PM
How can I make the reference link match the new formatting of its Suzanne Marie Excel Discussion (Misc queries) 1 August 18th 05 02:14 AM
Paste Link - retaining formatting Suzanne Marie Excel Discussion (Misc queries) 1 August 18th 05 02:02 AM
index & match with links for updating to other workbooks wrpalmer Excel Worksheet Functions 3 July 20th 05 03:18 PM


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

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017