hyperlinks to a 100 PDFs

[nilspreshaw]

I have a bunch of PDfs in a folder and am creating an Excel doc with a hyperlink for each PDF in a unique row (later I will insert work related notes about each PDF).

The PDfs in the folder are currently named 1 (1), 1 (2), 1 (3), etc...

I am using a formula that seems to work =HYPERLINK("file:///C:\Users\Nils Preshaw\Desktop\newton docs\Folder 1"&SUBSTITUTE(G13,G13,G14)& ".pdf").

I then write the name of the file ( i.e. 1 (1) ) in a neighboring cell and use another formula such as =HYPERLINK(I14,H14) in another cell and presto ! I can copy and past the above formula and hyperlinks appear to all my PDF docs!

The problem I have is I can't seem to figure out how to get around typing 1 (1) and then 1 (2) and then 1 (3) etc... manually.

I know that if you write "1, 2, 3" into individual cells on descending Rows you can use a mouse to drag lower magically increasing the numbering down the sheet but I can't seem to get this to work with the way the PDFs are currently named.

Can anyone suggest a formula I can use that will take 1 (1) and increase it to 1 (2) and so on?

Alternatively is there a way I can rename all the PDFs to something more workable?

many many thanks

Nils


<strike></strike>

 

[tonyyy]

Nils,

Welcome to the Board.

How 'bout this approach...

In F1 type "1("
In G1 type "1"
In H1 type ")"
In I1 type "=CONCATENATE(F1,G1,H1)"
Select F1:I1 and drag the +handle down...

If you want Column I to be constants rather than formulas, copy the column and paste as values.

Cheers,

tonyyy

 

[nilspreshaw]

thanks Tony !

genius

 

[tonyyy]

LOL! No, no genius here. But you're welcome just the same...