changing the name of a hyperlink cell when copied

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i have a hyperlink formula that will adjust when i copy it to the cell below
it
=hyperlink("#"&cell("address",sheet1!a1),"stk001")
stk001 is the name of the hyperlink cell in a1
when i copy this down the page the range adjusts but the cell name doesnt
change
i need it to change to stk002, stk003, etc.
can anybody help
thank you
brock
 
If you're formulas are starting in row 1:

=HYPERLINK("#"&CELL("address",Sheet1!A1),"stk"&TEXT(ROW(),"000"))

If you were starting in row 5, you could use:
=HYPERLINK("#"&CELL("address",Sheet1!A1),"stk"&TEXT(ROW()-4,"000"))
 
Hi,

Try this:

=HYPERLINK("#"&CELL("address",Sheet1!A1),"stk"&REPT("0",3-LEN(ROW()))&ROW())

be aware that this formula works if you are entering this formula in the
first row of your sheet as the first row formula otherwise you have to make 1
with row() in the formula for example if you want to enter this formula as
the first row formula in the row 10 you sould change the formula as follow:
=HYPERLINK("#"&CELL("address",Sheet1!A1),"stk"&REPT("0",3-LEN(ROW()-9))&ROW()-9) so you can copy drag down formula to where ever you need

Thanks,
 
thanx guys
dave you were very helpful as always
and farhad your solution works as well
thank you both for responding so quickly
 
Back
Top