Hyperlinking 2 Lists

[gruhnelliot]

Hello! I will make this as short as possible, I promise! I'm trying to hyperlink a list of numbers from one worksheet to another without having to do it manually.

For example, this table is in worksheet # 1.

[TABLE="width: 100"]
<tbody>[TR]
[TD]18-01[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]18-02[/TD]
[TD]Adam[/TD]
[/TR]
[TR]
[TD]18-03[/TD]
[TD]Dave[/TD]
[/TR]
</tbody>[/TABLE]

A similar table is in worksheet # 2. It has the same numbers (18-01, 18-02, etc.) but a completely different data set.

[TABLE="width: 200"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]Jan[/TD]
[TD="align: center"]Feb[/TD]
[TD="align: center"]Mar[/TD]
[/TR]
[TR]
[TD="align: center"]18-01[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]18-02[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[/TR]
</tbody>[/TABLE]

Is there a way to hyperlink these numbers

 

[DSCfromCFA]

Re: Need Help With Hyperlinking 2 Lists

G'day gruhnelliot,

Your questions lacks some details,

Hyperlink to or from what?
Which numbers? the 18- numbers or the 10's & 5's

It may be better to explain what you are trying to achieve.

Cheers

DSC

 

[DSCfromCFA]

Re: Need Help With Hyperlinking 2 Lists

Upon reflection, is what you are trying to achieve a combination of the two datasets that would, for example, have the name from ws1 appear between the 18- and the data from ws2

as in
A B C D E
Jan Feb Mar
18-01 John 10 5 10


If that is the case the question is where you want to do this? in ws1 , ws2 or another worksheet or workbook?

The easiest method that I am aware of would be a new column in ws2 where you want the name to appear (B above) and in that column simply put "=ws1 cell reference", and copy down. (I.e if John was in ws1 A2, then you can either type = and click in the source cell in ws1 OR type =worksheet 1 name<ws1><worksheetname>!A2

If you want it on a new worksheet or workbook then you have to link back to both sets of data, but is it very easily done with an "=" and a right mouse click in the cell from either ws1 or ws2 you want to copy into the new location.</worksheetname></ws1>

 

[gruhnelliot]

Re: Need Help With Hyperlinking 2 Lists

<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl63 {text-align:center; border:.5pt solid windowtext;}--></style>I apologize for the lack of details, I will try to explain it better here.

In worksheet # 1, I have a table where a group of people are assigned a file number, for example: John is assigned 18-01, Dave is assigned 18-02, and so on...

This is an example of the table in WS#1
[TABLE="width: 130"]
<!--StartFragment--> <colgroup><col width="65" span="2" style="width:65pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 65"]18-01[/TD]
[TD="class: xl63, width: 65"]John[/TD]
[/TR]
[TR]
[TD="class: xl63"]18-02[/TD]
[TD="class: xl63"]Dave[/TD]
[/TR]
[TR]
[TD="class: xl63"]18-03[/TD]
[TD="class: xl63"]Chris[/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]

In worksheet # 2, those same file numbers are in a table which show how much money each person has spent this year.

This is an example of the table in WS#2.
[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD="align: center"]File #[/TD]
[TD="align: center"]JAN[/TD]
[TD="align: center"]FEB[/TD]
[TD="align: center"]MAR[/TD]
[/TR]
[TR]
[TD="align: center"]18-01
[/TD]
[TD="align: center"]20$[/TD]
[TD="align: center"]10$[/TD]
[TD="align: center"]200$[/TD]
[/TR]
[TR]
[TD="align: center"]18-02[/TD]
[TD="align: center"]10$[/TD]
[TD="align: center"]50$[/TD]
[TD="align: center"]2000$[/TD]
[/TR]
[TR]
[TD="align: center"]18-03[/TD]
[TD="align: center"]0$[/TD]
[TD="align: center"]0$[/TD]
[TD="align: center"]0$[/TD]
[/TR]
</tbody>[/TABLE]


Is there a way to hyperlink each corresponding number (18-01, 18-02, and so on) from WS#1 to WS#2, and from WS#2 back to WS#1?

So, if I was in WS#1, my goal is to click "18-01" and it hyperlink me to "18-01" in WS#2. Does this make sense?

The list has hundreds of file numbers so manually doing it for each one isn't realistic. [TABLE="width: 260"]
<colgroup><col span="4"></colgroup><tbody>[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 260"]
<colgroup><col span="4"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[gruhnelliot]

Re: Need Help With Hyperlinking 2 Lists

I apologize for the lack of details, I will try to explain it better here.

In worksheet # 1, I have a table where a group of people are assigned a file number, for example: John is assigned 18-01, Dave is assigned 18-02, and so on...

This is an example of the table in WS#1
[TABLE="class: cms_table, width: 130"]
<tbody>[TR]
[TD="class: cms_table_xl63, width: 65"]18-01[/TD]
[TD="class: cms_table_xl63, width: 65"]John[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63"]18-02[/TD]
[TD="class: cms_table_xl63"]Dave[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63"]18-03[/TD]
[TD="class: cms_table_xl63"]Chris[/TD]
[/TR]
</tbody>[/TABLE]


In worksheet # 2, those same file numbers are in a table which show how much money each person has spent this year.

This is an example of the table in WS#2.
[TABLE="class: cms_table_grid, width: 300"]
<tbody>[TR]
[TD="align: center"]File #[/TD]
[TD="align: center"]JAN[/TD]
[TD="align: center"]FEB[/TD]
[TD="align: center"]MAR[/TD]
[/TR]
[TR]
[TD="align: center"]18-01[/TD]
[TD="align: center"]20$[/TD]
[TD="align: center"]10$[/TD]
[TD="align: center"]200$[/TD]
[/TR]
[TR]
[TD="align: center"]18-02[/TD]
[TD="align: center"]10$[/TD]
[TD="align: center"]50$[/TD]
[TD="align: center"]2000$[/TD]
[/TR]
[TR]
[TD="align: center"]18-03[/TD]
[TD="align: center"]0$[/TD]
[TD="align: center"]0$[/TD]
[TD="align: center"]0$[/TD]
[/TR]
</tbody>[/TABLE]



Is there a way to hyperlink each corresponding number (18-01, 18-02, and so on) from WS#1 to WS#2, and from WS#2 back to WS#1?

So, if I was in WS#1, my goal is to click "18-01" and it hyperlink me to "18-01" in WS#2. Does this make sense?

The list has hundreds of file numbers so manually doing it for each one isn't realistic.

 

[gruhnelliot]

Re: Need Help With Hyperlinking 2 Lists

I apologize for the lack of details, I will try to explain it better here.

In worksheet # 1, I have a table where a group of people are assigned a file number, for example: John is assigned 18-01, Dave is assigned 18-02, and so on...

This is an example of the table in WS#1
[TABLE="class: cms_table, width: 130"]
<tbody>[TR]
[TD="class: cms_table_xl63, width: 65"]18-01[/TD]
[TD="class: cms_table_xl63, width: 65"]John[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63"]18-02[/TD]
[TD="class: cms_table_xl63"]Dave[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63"]18-03[/TD]
[TD="class: cms_table_xl63"]Chris[/TD]
[/TR]
</tbody>[/TABLE]


In worksheet # 2, those same file numbers are in a table which show how much money each person has spent this year.

This is an example of the table in WS#2.
[TABLE="class: cms_table_grid, width: 300"]
<tbody>[TR]
[TD="align: center"]File #[/TD]
[TD="align: center"]JAN[/TD]
[TD="align: center"]FEB[/TD]
[TD="align: center"]MAR[/TD]
[/TR]
[TR]
[TD="align: center"]18-01[/TD]
[TD="align: center"]20$[/TD]
[TD="align: center"]10$[/TD]
[TD="align: center"]200$[/TD]
[/TR]
[TR]
[TD="align: center"]18-02[/TD]
[TD="align: center"]10$[/TD]
[TD="align: center"]50$[/TD]
[TD="align: center"]2000$[/TD]
[/TR]
[TR]
[TD="align: center"]18-03[/TD]
[TD="align: center"]0$[/TD]
[TD="align: center"]0$[/TD]
[TD="align: center"]0$[/TD]
[/TR]
</tbody>[/TABLE]



Is there a way to hyperlink each corresponding number (18-01, 18-02, and so on) from WS#1 to WS#2, and from WS#2 back to WS#1?

So, if I was in WS#1, my goal is to click "18-01" and it hyperlink me to "18-01" in WS#2.

The list has hundreds of file numbers so manually doing it for each one isn't realistic.