indirect function problems

[Guest]

i have a formula which works fine...

=HYPERLINK(("\\Gscc1\Rugby\Registration\Databases\Signatures\" &TEXT('Organisations A-F'!J10,"00000"), "Signature of"))

but ive tried manipulating it so it is dependand on the row... using the INDIRECT function:

=HYPERLINK(("\\Gscc1\Rugby\Registration\Databases\Signatures\" &TEXT(INDIRECT('Organisations A-F'!J&ROW(),"00000"), "Signature of")))

with this, i recieve the #NAME? error.... what is wrong with this formula???

the reason i want to do this, is becuase i have created a VB macro which copies this formula along with other information into a new record line every time a user fills out a user form.

Im aware i can write the first formula into row 2 and drag down..., but I did not want to select and drag the formula down the sheet up to row 65366 because this looks unprofessional and my clients get confused!

If you are unsure of what i mean i will explain in more detail, please reply.

any help would be much appreciated...

many thanks. camron

 

[Frank Kabel]

Hi
try
=HYPERLINK(("\\Gscc1\Rugby\Registration\Databases\Signatures\'Organisat
ions A-F'!J" & TEXT(ROW(),"00000"), "Signature of")))

 

[Guest]

Hi

Try using INDIRECT.EXT rather than INDIRECT. The EXT function will work on
closed workbooks - which could be where your problem is.

--
Andy.

i have a formula which works fine...

=HYPERLINK(("\\Gscc1\Rugby\Registration\Databases\Signatures\"

&TEXT('Organisations A-F'!J10,"00000"), "Signature of"))

but ive tried manipulating it so it is dependand on the row... using the INDIRECT function:

=HYPERLINK(("\\Gscc1\Rugby\Registration\Databases\Signatures\"

&TEXT(INDIRECT('Organisations A-F'!J&ROW(),"00000"), "Signature of")))

with this, i recieve the #NAME? error.... what is wrong with this formula???

the reason i want to do this, is becuase i have created a VB macro which

copies this formula along with other information into a new record line
every time a user fills out a user form.

Im aware i can write the first formula into row 2 and drag down..., but I

did not want to select and drag the formula down the sheet up to row 65366
because this looks unprofessional and my clients get confused!

 

[Guest]

this unfortunately still does not work...

all i am tryin to do is get

=HYPERLINK(("\\Gscc1\Rugby\Registration\Databases\Signatures\"&TEXT('Organisations A-F'!J6,"00000")),"Signature of")

to work off the row.... e.g.

=HYPERLINK(("\\Gscc1\Rugby\Registration\Databases\Signatures\"&TEXT('Organisations A-F'!J(ROW()),"00000")),"Signature of")

There must be a way! Im running excel 2000, UK version.

Your help is much appreciated

 

[Frank Kabel]

Try
=HYPERLINK("\\Gscc1\Rugby\Registration\Databases\Signatures\"&TEXT(INDI
RECT("'Organisations A-F'!J" & ROW()),"00000"),"Signature of")

 

[Guest]

frank, you are the man. it works.. thank you!

however, now i have another problem...

the speech " marks in the formula interfere with VB...

e.g.

activecell.offset(0, 1) = " =HYPERLINK("\\Gscc1\Rugby\Registration\Databases\Signatures\"&TEXT(INDIRECT("'Organisations A-F'!J" & ROW()),"00000"),"Signature of") "

i get a compile error at the 1st speech mark - "expected: expression"

is there any way i can separate the formula from the outside speech marks?