changing the name of a hyperlink cell when copied

[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

 

[Dave Peterson]

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"))

 

[Guest]

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,

 

[Guest]

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