Wildcards in hyperlink formulas

[Guest]

At the moment, I have a spreadsheet that is an index to filed documents, with
a primary key of a number in column A. All the files are stored with the
number of the file as the filename. Eg G:\Research &
Resources\DATABASE\F23.pdf. Assuming my data is in cell A1, If I use the
formula =HYPERLINK(CONCATENATE("G:\Research &
Resources\DATABASE\F",A1,".pdf")), then it works well.

That's fine if they're all pdf documents, but there are excel spreadsheets,
and jpeg files, word documents and even folders. Is there a way that I can
get it to launch any or all things starting with what's what's in A1? I
tried =HYPERLINK(CONCATENATE("G:\Research & Resources\DATABASE\F",A1,".???"))
but it doesn't work. Does this mean that I can't use wildcards in formulas?
Is there any other way around this problem so that it will launch any file in
the correct folder starteing with the number in A1? Any help would be much
appreciated.

 

[zz]

maybe you can try

=hyperlink(concatenate(B1,C1,D1,A1,E1))

in B1 you would store the initial part of the path i'm assuming it's
"G:\\research"

then in C1 you would store "\resources"

in D1 it is "\DATABASE\F"

in A1 i'm assuming that you store the filename number let it be
"filename_number"

in E1 you should sotore the extension of the file you want to open with the
dot included eg ".xls",".doc",".xml", etc.

the final result should aproach this: "
G:\\research\resources\DATABASE\Ffilename_number.pdf "




just one final question on the path of the files, the "F" part isn't another
folder?

because if it is then this correction must be applied

in D1 it is "\DATABASE\F\"

 

[Guest]

Thanks for that. That crossed my mind, but I was hoping to be able to avoid
that, because there are a lot of people who will be adding to this
spreadsheet, and chances are they propbably won't even know what filetype
their document is. I guess I could go into the spreadsheet every now and then
and fill in the field.

F is just a way of indicating that the file is part of the database. The
primary key is called the folio number, so F stands for "Folio", and it is at
the start of every file in the folder. eg "F47.jpeg"

Thanks for the help anyway.

maybe you can try

=hyperlink(concatenate(B1,C1,D1,A1,E1))

in B1 you would store the initial part of the path i'm assuming it's
"G:\\research"

then in C1 you would store "\resources"

in D1 it is "\DATABASE\F"

in A1 i'm assuming that you store the filename number let it be
"filename_number"

in E1 you should sotore the extension of the file you want to open with the
dot included eg ".xls",".doc",".xml", etc.

the final result should aproach this: "
G:\\research\resources\DATABASE\Ffilename_number.pdf "




just one final question on the path of the files, the "F" part isn't another
folder?

because if it is then this correction must be applied

in D1 it is "\DATABASE\F\"


--
---
zz [MX]
cuasi-musico,semi-poeta y loco

At the moment, I have a spreadsheet that is an index to filed documents,
with
a primary key of a number in column A. All the files are stored with the
number of the file as the filename. Eg G:\Research &
Resources\DATABASE\F23.pdf. Assuming my data is in cell A1, If I use the
formula =HYPERLINK(CONCATENATE("G:\Research &
Resources\DATABASE\F",A1,".pdf")), then it works well.

That's fine if they're all pdf documents, but there are excel
spreadsheets,
and jpeg files, word documents and even folders. Is there a way that I
can
get it to launch any or all things starting with what's what's in A1? I
tried =HYPERLINK(CONCATENATE("G:\Research &
Resources\DATABASE\F",A1,".???"))
but it doesn't work. Does this mean that I can't use wildcards in
formulas?
Is there any other way around this problem so that it will launch any file
in
the correct folder starteing with the number in A1? Any help would be much
appreciated.