indirect function problems

C

camron107

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
 
F

Frank Kabel

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

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.


camron107 said:
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!
 
C

camron107

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
 
F

Frank Kabel

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

camron107

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top