list of hyperlinks to a row

[quartzz]

Hi,

I have something like the following

xl link.png

the hyperlinks might go to Z and more (at last count there were at least 50, making it 'AZ1' and more)

is there a quicker way to create the hyperlinks in column A, than to manually edit each one? eg can the hyperlinks be set to "next column after the last hyperlink, for eg

3A, hyperlinks to D1
4A, hyperlinks to "what 3A was linked to, plus 1" -- ie, E1
5A, hyperlinks to "what 4A was linked to, plus 1" -- ie, F1

( or something along those lines )

or

is it possible to link one cell (3A) to another cell (D1) with something similar to "ctrl-click 2 cells"? ie, to link just by selecting both cells, with some key combination

thanx in advance

[BMV]

Please Login or Register  to view this content.

[quartzz]

that seems to link thanks

can I enter a visible value in the cell that isn't the destination cell?

also very usefully, it seems that I can insert a column, and insert a row in the link column to add in a new link - that's good

[BMV]

can I enter a visible value in the cell that isn't the destination cell?

Yes you can. Change second parameter of hyperlink function.

[quartzz]

which is....which part of the =hyperlink("#"&address(1,row()+1),address(1,row()+1,4)) string? thanx

[BMV]

This one address(1,row()+1,4) - its friendly name.

[quartzz]

thanks - changed to =HYPERLINK("#"&ADDRESS(1,ROW()+1),test) , and the cell came up with #NAME?

[BMV]

it must be "test"

[quartzz]

thanks. I can see that will automatically hyperlink to the next column for the next row, I will still have to manually enter in the " [friendly name] " ?

a next question would be, is there any way, if I have column A already filled in with names, I can put in the =Hyperlink code, and have it use the column A name as the [friendly name] ?

or as an alternate to the =hyperlink code, is there a way to create a link with a key+mouse click combination

eg (I'm thinking up a totally top-of-my-head key combo here)

click column A cell, then "ctrl-h plus mouse click" to destination cell (where ctrl-h is 'create hyperlink')

[BMV]

Please Login or Register  to view this content.

[quartzz]

hello thanks, that gives me '0' visible in the cell?

would something like

=HYPERLINK("#"&ADDRESS(1,ROW()+1),"=B10")

work (I know it wouldn't because I've just tried it)

so the hyperlink cell would be A10, and the visible name content of the cell would be B10

(I'd basically have two columns, one of hyperlinks and one of the names)

[AliGW]

Did you try this:

=HYPERLINK("#"&ADDRESS(1,ROW()+1),B10)

[quartzz]

ah. outstandin.

dragging the square at the lower-right hand corner of the box also seems to increment the [friendly name] link number too, which is good (so I won't have to manually edit each [friendly name] cell ref

so at the moment I have

=HYPERLINK("#"&ADDRESS(1,ROW()+1),B10)
=HYPERLINK("#"&ADDRESS(1,ROW()+1),B11)
=HYPERLINK("#"&ADDRESS(1,ROW()+1),B12)

having two columns, the existing one of the names (Bxx) and the hyperlinked ones (Axx) will probably be fine

if I was being picky..... is it possible to edit the Bxx column list, to basically add

=HYPERLINK("#"&ADDRESS(1,ROW()+1), ****** )

to the existing data? (so I would just have a single name/hyperlink list)

where ***** is the existing text name

[AliGW]

Sorry - I don't get what you want.

[quartzz]

sure; basically, this is ok

Attachment 663154

but if I could "tidy it up" a bit by having just a single column Axx of links, it'd be 'nice'. I know I could hide or narrow the Bxx names column, but I prefer not hiding columns because you forget they are there and have to "unhide" the to edit them etc

so if I could edit the existing names column to be

=HYPERLINK("#"&ADDRESS(1,ROW()+1), ****** )

where ***** is the existing value, it would be nice

something like

=HYPERLINK("#"&ADDRESS(1,ROW()+1), "current_value" ) ?

[BMV]

quartz, Could you attach XLS file with short example where you show what you would like to see.

[quartzz]

hello, attached

sheet1 has a hidden column, which I'd like to remove

sheet2 is an example of where I'd like to be (I've manually entered 6 name links)
sheet2 I've needed to have a blank 'B' column to keep the links valid

ooook...for some reason it won't let me do the dropdown attachment button?

xl scrn.png

[BMV]

quartz, Please follow the instruction "Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen." You can read it on the yellow banner on the top of web screen.

[quartzz]

Hello,

I've just had a think about it actually.

the 2 sheets I was considering were sheet1 and sheet2 attached

sheet1 has a hidden column B, which I don't really want
sheet2 is closer to what I want - just a single column of " named " links

but to create sheet2 column A hyperlinks out of the existing text in them, I would have to fill out all the values of column A as

=HYPERLINK("#"&ADDRESS(1,ROW()+1), ***existing text in column A cell*** )

I don't know if the above is poss

thinking about it, I've actually just thought of another way of doing it, on sheet3

I've filled out sheet3's Column A manually for 5 links

the only problem is, when I drag the cells lower right hand box down, instead of filling column A with

=HYPERLINK("#"&ADDRESS(1,ROW()+1),C1)
=HYPERLINK("#"&ADDRESS(1,ROW()+1),D1)
=HYPERLINK("#"&ADDRESS(1,ROW()+1),E1)
=HYPERLINK("#"&ADDRESS(1,ROW()+1),F1)

which links column A to Row 1 (which is what I want)

it fills them in with

=HYPERLINK("#"&ADDRESS(1,ROW()+1),C1)
=HYPERLINK("#"&ADDRESS(1,ROW()+1),C2)
=HYPERLINK("#"&ADDRESS(1,ROW()+1),C3)
=HYPERLINK("#"&ADDRESS(1,ROW()+1),C4)

does that make.....sense ?

(I think I've attached xls properly?)

[BMV]

I think you skipped #10.

[quartzz]

ok - (#10 being this thread ) that uses row1 as the source for each text name (as opposed to Column B). I think I can work with that. that would also double check that I only have each name written once, which would make sure the names are synced