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



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

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,
--
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

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"&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,
> --
> 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