Hyperlinked index?

=HYPERLINK("C:\images\batch2\0" &ROW()+242&".jpg")

then copy and paste special as a value.


(edit the 242 to match the row you start on.) 242 assumes you are starting on row 2

Thanks for the quick reply, kgkev - I'll have a play around with that and let you know how I get on.

Hi kgkev and everyone else!

=HYPERLINK("C:\images\batch2\0" &ROW()+242&".jpg") plus copy and paste works nicely to generate the correctly named list of hyperlinks, so thanks for that. I even understand how it works, which is a bonus!

I didn't know one could paste into a range of cells to autofill them - very cool.

Just a cosmetic thing, I hope, but at the moment I've got the links showing up in a column to the right of the index entries; ideally I would like each cell itself to be hyperlinked, and the paths not to be present as an additional column at all.

Ie the only entries would be along the lines of:

Doe John|London|Male
Doe Jane|New York|Female

where each of the six cells is a hyperlink, the first row to John Doe's photo, and the second row to Jane Doe's photo.

Is that do-able? Thanks in advance for any thoughts.

Assuming The Name is in A1

=HYPERLINK("C:\images\batch2\0" &ROW()+242&".jpg",A1)

Then Copy and Paste Special into A1(or wherever)

Explanation....

=hyperlink("LOCATION",Friendly Name")

So you will end up with john doe So if clicked you will see a picture

Success!

kgkev said:

Assuming The Name is in A1

=HYPERLINK("C:\images\batch2\0" &ROW()+242&".jpg",A1)

Then Copy and Paste Special into A1

Click to expand...

kgkev, I found that Copy and Paste Special retains the result of the calculation, but loses the hyperlink - it just outputs in standard unlinked text - but I can imagine adding in the hyperlink after the calc would work.

No problem, though; instead I used the dos command

dir c:\images\batch2\ /b >c:\batch2list.xls

to output all the file names immediately as a column in an Excel spreadsheet. That will even work if ever they are non-sequential!

I then used your formula to make A1 etc the output, but created the input A1 on sheet 2 to avoid circularity. Have I understood correctly?

So you will end up with john doe So if clicked you will see a picture

Click to expand...

Yes!

I had hoped that the hyperlinks would be "self-contained", ie without the need to refer to other cells elsewhere behind the scenes. I had already achieved this cell-by-cell before getting in touch, by using "insert hyperlink" each time. However, in that case the hyperlinks appear to behave differently, being part of the cell format rather than the content, so they don't move with their respective cells when resorting the rows. Anyone, correct me if I'm wrong about this.

In any case, any further guidance on how to do it en masse is welcomed! Great site & thanks again!

Suppose
A1 contains your Name (FRED)
B1 Contains Your Link (c:\images\batch2\0244.JPG)

Put



in C1 this will result in =hyperlink("C:\images\batch2\0244.jpg","FRED")

Copy and Paste special this into any other cell - this is now a self contained formula.

This is where my problems start.

The only way I could get this to change to an actual formula was to

F2, return -> down the list ok if you have 100 but not 1000 (your fingers will hurt)


OR

Copy and paste all the cells into NotePad

The then copy and paste them back - Should work but you might have to set the formating straight

Copy and Paste Special (formats) from a real hyperlink to sort this out.

Nice one, kgkev! An elegant simplification.

It works well - & I tweaked Copy & Paste in and out of Notepad as you suggested.

My original thinking was not to show the numeric image file names, since they are "internal" to the application and there is no obvious reason for the user to know them; they simply reflect the order in which the records/images were created.

However, it can be helpful to the end user to see them after all. Eg when we deliver multiple batches of images for import into a database and they need to know where one ends and the next starts. Therefore your straightforward method is spot on for what is required.

kgkev, you are a star!