changing the name of a hyperlink cell when copied
 

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

changing the name of a hyperlink cell when copied
 

If you're formulas are starting in row 1:

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

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



BROCK8292 wrote:

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

changing the name of a hyperlink cell when copied
 

Hi,

Try this:

=HYPERLINK("#"&CELL("address",Sheet1!A1),"stk"&REP T("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"&REP T("0",3-LEN(ROW()-9))&ROW()-9) so you can copy drag down formula to where ever you need

Thanks,
--
Farhad Hodjat


"BROCK8292" wrote:

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

changing the name of a hyperlink cell when copied
 

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


"Farhad" wrote:

Hi,

Try this:

=HYPERLINK("#"&CELL("address",Sheet1!A1),"stk"&REP T("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"&REP T("0",3-LEN(ROW()-9))&ROW()-9) so you can copy drag down formula to where ever you need

Thanks,
--
Farhad Hodjat


"BROCK8292" wrote:

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