Hyperlink/Fill series

[David Collings]

I hope somebody can help. I am using a speed sheet with over 3000 rows. Each row has a different number and they do run in sequence. What I am trying to do is hyperlink the first cell from each row (A1,B1,C1) and so on. I was hoping then to click and drag to create a fill series but it does not seem to work because the link has .pdf at the end of the file name so the number before that will not change. All I seem to get is the same file name. See sample file:
C:\clients\JoBloggs\11.09.06\test\A0101.pdf. It is the number 101 that needs to change automatically to 102 then 103 and so on as I drag down the page.

 

[mtb'r]

I would use a macro:

For Each cl In Range("1:1")
If cl <> "" Then
inc = Format(cl.column + 100, "0###")
ActiveSheet.Hyperlinks.Add Anchor:=cl, Address:= _
"C:\clients\JoBloggs\11.09.06\test\A" & inc & ".pdf"
End If
Next cl

Edit: oops - original reply filled rows. After rereading, I see that you want to fill columns. Post was updated accordingly.

 

[erik.van.geit]

Hi, David Collings
Welcome to the Board !!!!!

a non macro-approach
(despite they know me here as VBA-freak )

this would be the basics
you can change layout to your suits
see helpfiles for HYPERLINK
  A               B                                  C     D     
1 Path            C:\clients\JoBloggs\11.09.06\test\             
2 FIleName Root   AO                                             
3 Extension       pdf                                            
4 starting number 101                                            
5 AO101           AO102                              AO103 AO104 

testsheet
[Table-It] version 06 by Erik Van Geit

RANGE FORMULA (1st cell)
A5:D5 =HYPERLINK($B$1&$B$2& COLUMN(A5)-1+$B$4 & "."&$B$3,$B$2& COLUMN(A5)-1+$B$4)

[Table-It] version 06 by Erik Van Geit

kind regards,
Erik