Hyperlink & Vlookup help

[Elmer]

OK I have a big problem and nobody has been able to solve it yet.

I have my archive music charts in word format and I want to use excel. Simple enough I just copy and paste the chart onto excel. Thats easy. I also have songs into mp3 format hyperlinked from the name on the word document to play my mp3 when I click on it.

My problem starts when I use the Vlookup function so I only have to type in last weeks chart positions it copies the names OK but it doesn't trannpose the hyperlink. Can anyone help

e.g top 5 3-1-63

1 2 THE NEXT TIME / BACHELOR BOY Cliff Richard
2 1 RETURN TO SENDER Elvis Presley
3 5 LOVESICK BLUES Frank Ifield
4 7 SUN ARISE Rolf Harris
5 4 DANCE WITH THE GUITAR MAN Duane Eddy

then I use a Vlookup command and the top 5 the following week looks like this


1 1 THE NEXT TIME / BACHELOR BOY Cliff Richard
2 2 RETURN TO SENDER Elvis Presley
3 7 DANCE ON Shadows
4 5 DANCE WITH THE GUITAR MAN Duane Eddy
5 3 LOVESICK BLUES Frank Ifield

The vlookup has worked but the hyperlinks havent!!

Any experts out there ?

 

[Andrew Poulsom]

I don't see any hyperlinks in your post.

What formula are you using to produce the second list from the first?

 

[Elmer]

I'm sorry I've not made it very clear I will put it in full!!

I have an old top 40 chart!!

3rd January 1963
A B C

1 2 THE NEXT TIME / BACHELOR BOY Cliff Richard
2 1 RETURN TO SENDER Elvis Presley
3 5 LOVESICK BLUES Frank Ifield
4 7 SUN ARISE Rolf Harris
5 4 DANCE WITH THE GUITAR MAN Duane Eddy
6 9 BOBBY'S GIRL Susan Maughan
7 3 DANCE ON Shadows
8 15 IT ONLY TOOK A MINUTE Joe Brown
9 8 TELSTAR Tornados
10 10 LET'S DANCE Chris Montez
11 12 SWISS MAID Del Shannon
12 6 ROCKIN' AROUND THE CHRISTMAS TREE Brenda Lee
13 19 A FOREVER KIND OF LOVE Bobby Vee
14 22 UP ON THE ROOF Kenny Lynch
15 13 YOUR CHEATING HEART Ray Charles
16 14 DEVIL WOMAN Marty Robbins
17 28 GO AWAY LITTLE GIRL Mark Wynter
18 11 DESAFINADO Stan Getz And Charlie Byrd
19 29 LIKE I DO Maureen Evans
20 16 THE MAIN ATTRACTION Pat Boone
21 20 ME AND MY SHADOW Frank Sinatra And Sammy Davis Jnr
22 23 BABY TAKE A BOW Adam Faith
23 21 MUST BE MADISON Joe Loss Orchestra
24 17 LOVE ME DO Beatles
25 35 GOSSIP CALYPSO Bernard Cribbins
26 24 ISLAND OF DREAMS Springfields
27 25 SHERRY Four Seasons
28 27 LOVE ME TENDER Richard Chamberlain
29 18 WE'RE GONNA GO FISHIN' Hank Locklin
30 26 HE'S A REBEL Crystals
31 NEW DON'T YOU THINK IT'S TIME Mike Berry And The Outlaws
32 NEW LET'S GO Routers
33 NEW UP ON THE ROOF Julie Grant
34 30 I REMEMBER YOU Frank Ifield
35 NEW ALWAYS YOU AND ME Russ Conway
36 NEW CAN CAN' 62 Peter Jay And The Jaywalkers
37 39 DEAR LONELY HEARTS Nat King Cole
38 37 SUSIE DARLIN' Tommy Roe
39 33 LIMBO ROCK Chubby Checker
40 NEW KEEP YOUR HANDS OFF MY BABY Little Eva

No.1 equates to column 4 on the spreadsheet and number 40 column 43

For the second weeks chart I type in all the numbers from 1 to 40 in column A and all the last weeks chart positions in column B. The for the first record I use the following formula

=VLOOKUP(B49,$A$4:$C$43,3,FALSE)

with the last weeks chart position being in column B49 e.g

10th January 1963
A B C

(49)1 1 THE NEXT TIME / BACHELOR BOY Cliff Richard
(50)2 2 RETURN TO SENDER Elvis Presley
(51)3 7 DANCE ON Shadows
4 5 DANCE WITH THE GUITAR MAN Duane Eddy
5 3 LOVESICK BLUES Frank Ifield
6 8 IT ONLY TOOK A MINUTE Joe Brown
7 4 SUN ARISE Rolf Harris
8 17 GO AWAY LITTLE GIRL Mark Wynter
9 6 BOBBY'S GIRL Susan Maughan
10 19 LIKE I DO Maureen Evans
11 9 TELSTAR Tornados
12 14 UP ON THE ROOF Kenny Lynch
13 10 LET'S DANCE Chris Montez
14 11 SWISS MAID Del Shannon
15 18 DESAFINADO Stan Getz And Charlie Byrd
16 15 YOUR CHEATING HEART Ray Charles
17 24 LOVE ME DO Beatles
18 13 A FOREVER KIND OF LOVE Bobby Vee
19 30 HE'S A REBEL Crystals
20 20 THE MAIN ATTRACTION Pat Boone
21 31 DON'T YOU THINK IT'S TIME Mike Berry And The Outlaws
22 #N/A
23 21 ME AND MY SHADOW Frank Sinatra And Sammy Davis Jnr
24 #N/A
25 16 DEVIL WOMAN Marty Robbins
26 26 ISLAND OF DREAMS Springfields
27 23 MUST BE MADISON Joe Loss Orchestra
28 #N/A
29 22 BABY TAKE A BOW Adam Faith
30 40 KEEP YOUR HANDS OFF MY BABY Little Eva
31 25 GOSSIP CALYPSO Bernard Cribbins
32 29 WE'RE GONNA GO FISHIN' Hank Locklin
33 32 LET'S GO Routers
34 33 UP ON THE ROOF Julie Grant
35 #N/A
36 12 ROCKIN' AROUND THE CHRISTMAS TREE Brenda Lee
37 28 LOVE ME TENDER Richard Chamberlain
38 27 SHERRY Four Seasons
39 #N/A
40 36 CAN CAN' 62 Peter Jay And The Jaywalkers



Now pretend all the songs in the first chart have hyperlink to other areas, in may case the songs are stored on my hard drive. We I use the Vlookup function the hyperlinks don't transfer across and I only have the song titles without links to anywhere. By the way the N/A's are new entries which I would type in manually

 

[Andrew Poulsom]

So is eg?

THE NEXT TIME / BACHELOR BOY Cliff Richard

a Hyperlink? If so to what?

 

[Elmer]

Well that example is difficult because it is two songs in one cell but

e.g RETURN TO SENDER Elvis Presley

On the first chart it links to

C:/my documents/quiz/1960s/1962/1 - Elvis Presley - Return To Sender(62).mp3

but using Vlookup the link doesn't transpose to the second chart

 

[Andrew Poulsom]

I don't think you can get a hyperlink with a formula like that. I think this macro will do what you want:

Sub Test()
    Dim c As Range
    Dim WF As WorksheetFunction
    Dim r As Integer
    Set WF = WorksheetFunction
    For Each c In Range("B49:B88")
        If Not WF.IsError(c.Value) Then
            r = WF.Match(c.Value, Range("A4:A43"), 0)
            Range("C4:C43").Cells(r, 1).Copy c.Offset(0, 1)
        Else
            c.Offset(0, 1).ClearContents
        End If
    Next c
End Sub

You can assign it to a button if you like.

 

[Elmer]

I'm a complete beginner using macros. How do I go about assigning to my charts!!

 

[Andrew Poulsom]

Right click any ToolBar and click Control ToolBox. Click the CommandButton icon and click and drag to size and position it on your worksheet. Right click the CommandButton and choose View Code. You will get this:

Private Sub CommandButton1_Click()

End Sub

Paste my code in there (excluding its Sub and End Sub lines). Press Alt+F11 to return to your worksheet. Right click the CommandButton again and choose Properties. Change the Caption property to something more meaningful than CommandButton1 and close the Properties window. Click the Design icon (top left) in the Control ToolBox to exit design mode and close it.

Click the button to see if it works.

 

[Elmer]

I think I have got somewhere there. So if I was to do it for multpile charts in the future I have to re-write the macro each time for different cells

 

[Andrew Poulsom]

I think I have got somewhere there. So if I was to do it for multpile charts in the future I have to re-write the macro each time for different cells

Yes.