Excel Help

[austin316]

Ok, I'm getting extremely frustated and I think it is due to the fact that I don't know the syntax that is needed. Here is what I would like to do.


For the following function
=HYPERLINK("[Vouchers v020309 master backup.xls]'012809'!E2", "link")

I want to change the hard coding of the file name to something that is dynamic. To do this, I used the filename function and now in cell A1, it displays the text "Vouchers v020309 master backup.xls" (without the quotes of course).

However, when I try the following function, I get errors:
=HYPERLINK("[A1]'012809'!E2", "link")

Obvi, my problem is syntactical, but I can't pinpoint the problem. I've tried every combination of & signs and quotes that I can think of and it still won't work. Any help?

 

[Ken g6]

If I'm reading this right, you need to think in terms of concatenating strings:

=HYPERLINK("["&A1&"]'012809'!E2", "link")

 

[austin316]

Originally posted by: Ken g6
If I'm reading this right, you need to think in terms of concatenating strings:

=HYPERLINK("["&A1&"]'012809'!E2", "link")

I get an error message "Can not open the file" with that code.

 

[Ken g6]

OK, in another cell, try:

="["&A1&"]'012809'!E2"

And compare it to your original string.

You might also try:

=HYPERLINK("["&trim(A1)&"]'012809'!E2", "link")

 

[austin316]

Originally posted by: Ken g6
OK, in another cell, try:

="["&A1&"]'012809'!E2"

And compare it to your original string.

You might also try:

=HYPERLINK("["&trim(A1)&"]'012809'!E2", "link")

got it! thanks! I didn't need to include the [ and ] brackets.