How do I make the filename in a link variable based on a cell valu

Hello,
Please bear with me - I am a long term excel user but I am new to Questions
and I may not have phrased this very well.. I am using Excel 2000 on a work
computer. I apologise if this has already been answered - I did see some
similar questions but none which explained how to do this.

I have made an application form spreadsheet to send out to new teachers.
When they are sent back they are saved in the same folder under teachers'
name, ie JohnSmith.xls, AnnabelleGreen.xls etc. Their data is compiled from
the form in a single column in a hidden sheet called Data in each workbook.

I want to make a database workbook (in the same folder) which will take
information from all of these data sheets in each file and keep them together
on one sheet so that we can run mailmerges etc.

I could copy and paste the data, but I would like it to update
automatically. I would like them to be able to save their application form,
enter the name once on an index sheet, and the database would update with the
info from the new application. I would also like not to use macros as our
council computers crash a lot, if possible I would like to do this entirely
with formulas and links. (I realise this would be easy with a "find and
replace" macro).

I can link to any of the teacher applications once I know their name - with
='[JohnSmith.xls]Data'!B2 etc.

Basically I would like the "Johnsmith.xls" bit to be variable based on what
I type into a cell in an index of teachers, so that in the index I will have
cell a1 "johnsmith.xls", cell a2 "annabellegreen.xls", and in my database
sheet I will have "='[Index!a1]Data'!B2" which will point to cell b2 in
sheet "data" of workbook "Johnsmith.xls"
and
"='[Index!a2]Data'!B2" which will point to cell b2 in sheet "data" of
workbook "Annabellegreen.xls"

However I can't find a way of making the filename in a link variable based
on the cell value.


Is there a way of doing this? It feels like there should be, but I can't
find a way. I thought the "substitute" command might work but it seems it
only operates on the result of a formula and not on the actual text of the
formula itself.

Many thanks to anyone who can sort this out!

Bekki

Bekki,

The easiest way to do this without using macros is to set up one set of formulas, using the form

='[JohnSmith.xls]Data'!$B$2

(with absolute cell addressing - the $s are needed for this)

and then copy all those formulas to as many rows as you need. Then use Find and Replace to replace
JohnSmith with AnnabelleGreen, etc. in that one row, and so on adnaseum.

And that's why macros are handy....

HTH,
Bernie
MS Excel MVP


"Bekki May" Bekki wrote in message
...
Hello,
Please bear with me - I am a long term excel user but I am new to Questions
and I may not have phrased this very well.. I am using Excel 2000 on a work
computer. I apologise if this has already been answered - I did see some
similar questions but none which explained how to do this.

I have made an application form spreadsheet to send out to new teachers.
When they are sent back they are saved in the same folder under teachers'
name, ie JohnSmith.xls, AnnabelleGreen.xls etc. Their data is compiled from
the form in a single column in a hidden sheet called Data in each workbook.

I want to make a database workbook (in the same folder) which will take
information from all of these data sheets in each file and keep them together
on one sheet so that we can run mailmerges etc.

I could copy and paste the data, but I would like it to update
automatically. I would like them to be able to save their application form,
enter the name once on an index sheet, and the database would update with the
info from the new application. I would also like not to use macros as our
council computers crash a lot, if possible I would like to do this entirely
with formulas and links. (I realise this would be easy with a "find and
replace" macro).

I can link to any of the teacher applications once I know their name - with
='[JohnSmith.xls]Data'!B2 etc.

Basically I would like the "Johnsmith.xls" bit to be variable based on what
I type into a cell in an index of teachers, so that in the index I will have
cell a1 "johnsmith.xls", cell a2 "annabellegreen.xls", and in my database
sheet I will have "='[Index!a1]Data'!B2" which will point to cell b2 in
sheet "data" of workbook "Johnsmith.xls"
and
"='[Index!a2]Data'!B2" which will point to cell b2 in sheet "data" of
workbook "Annabellegreen.xls"

However I can't find a way of making the filename in a link variable based
on the cell value.


Is there a way of doing this? It feels like there should be, but I can't
find a way. I thought the "substitute" command might work but it seems it
only operates on the result of a formula and not on the actual text of the
formula itself.

Many thanks to anyone who can sort this out!

Bekki

Thank you for your reply!

I was hoping to have them update automatically from the index - so that the
user, who will not be me but an HR person who has hundreds of forms to enter
and does not like excel much, can just write "John Smith.xls" in cell A1 of
the index sheet, "Anabelle Green.xls" in cell A2 etc, and then the database
sheet will link to the relevant columns in johnsmith.xls and
anabellegreen.xls, which I have previously set up to refer to cells A1 and A2
in the Index sheet... so that the end user only has to write the name of each
file he has saved once, in the index.. I like to make my spreadsheets so that
the user has to do as little as possible, so that there is less to break.

Is this not possible? It sounds from your reply as if it isn't and the only
option is to do a find and replace every time, or make a macro to do a find
and replace for him (which would be a perfect option if the council firewall
didn't appear to object to them). I remain hopeful that there is a way to put
a cell reference in a link and I am just not explaining it very well!

Thank you again for taking the time to reply to me,
Bekki


"Bernie Deitrick" wrote:

Bekki,

The easiest way to do this without using macros is to set up one set of formulas, using the form

='[JohnSmith.xls]Data'!$B$2

(with absolute cell addressing - the $s are needed for this)

and then copy all those formulas to as many rows as you need. Then use Find and Replace to replace
JohnSmith with AnnabelleGreen, etc. in that one row, and so on adnaseum.

And that's why macros are handy....

HTH,
Bernie
MS Excel MVP


"Bekki May" Bekki wrote in message
...
Hello,
Please bear with me - I am a long term excel user but I am new to Questions
and I may not have phrased this very well.. I am using Excel 2000 on a work
computer. I apologise if this has already been answered - I did see some
similar questions but none which explained how to do this.

I have made an application form spreadsheet to send out to new teachers.
When they are sent back they are saved in the same folder under teachers'
name, ie JohnSmith.xls, AnnabelleGreen.xls etc. Their data is compiled from
the form in a single column in a hidden sheet called Data in each workbook.

I want to make a database workbook (in the same folder) which will take
information from all of these data sheets in each file and keep them together
on one sheet so that we can run mailmerges etc.

I could copy and paste the data, but I would like it to update
automatically. I would like them to be able to save their application form,
enter the name once on an index sheet, and the database would update with the
info from the new application. I would also like not to use macros as our
council computers crash a lot, if possible I would like to do this entirely
with formulas and links. (I realise this would be easy with a "find and
replace" macro).

I can link to any of the teacher applications once I know their name - with
='[JohnSmith.xls]Data'!B2 etc.

Basically I would like the "Johnsmith.xls" bit to be variable based on what
I type into a cell in an index of teachers, so that in the index I will have
cell a1 "johnsmith.xls", cell a2 "annabellegreen.xls", and in my database
sheet I will have "='[Index!a1]Data'!B2" which will point to cell b2 in
sheet "data" of workbook "Johnsmith.xls"
and
"='[Index!a2]Data'!B2" which will point to cell b2 in sheet "data" of
workbook "Annabellegreen.xls"

However I can't find a way of making the filename in a link variable based
on the cell value.


Is there a way of doing this? It feels like there should be, but I can't
find a way. I thought the "substitute" command might work but it seems it
only operates on the result of a formula and not on the actual text of the
formula itself.

Many thanks to anyone who can sort this out!

Bekki

Bekki,

The typical solution would be

=INDIRECT("'C:\Path\[" & A1 & "]Data'!$B$2")

with the filename in A1.

But that will not work unless the file named in A1 is open. All other solutions that I know of are
macro based.

Firewalls will ignore what is already behind them, so why won't macros work?

HTH,
Bernie
MS Excel MVP


"Bekki May" wrote in message
...
Thank you for your reply!

I was hoping to have them update automatically from the index - so that the
user, who will not be me but an HR person who has hundreds of forms to enter
and does not like excel much, can just write "John Smith.xls" in cell A1 of
the index sheet, "Anabelle Green.xls" in cell A2 etc, and then the database
sheet will link to the relevant columns in johnsmith.xls and
anabellegreen.xls, which I have previously set up to refer to cells A1 and A2
in the Index sheet... so that the end user only has to write the name of each
file he has saved once, in the index.. I like to make my spreadsheets so that
the user has to do as little as possible, so that there is less to break.

Is this not possible? It sounds from your reply as if it isn't and the only
option is to do a find and replace every time, or make a macro to do a find
and replace for him (which would be a perfect option if the council firewall
didn't appear to object to them). I remain hopeful that there is a way to put
a cell reference in a link and I am just not explaining it very well!

Thank you again for taking the time to reply to me,
Bekki


"Bernie Deitrick" wrote:

Bekki,

The easiest way to do this without using macros is to set up one set of formulas, using the form

='[JohnSmith.xls]Data'!$B$2

(with absolute cell addressing - the $s are needed for this)

and then copy all those formulas to as many rows as you need. Then use Find and Replace to
replace
JohnSmith with AnnabelleGreen, etc. in that one row, and so on adnaseum.

And that's why macros are handy....

HTH,
Bernie
MS Excel MVP


"Bekki May" Bekki wrote in message
...
Hello,
Please bear with me - I am a long term excel user but I am new to Questions
and I may not have phrased this very well.. I am using Excel 2000 on a work
computer. I apologise if this has already been answered - I did see some
similar questions but none which explained how to do this.

I have made an application form spreadsheet to send out to new teachers.
When they are sent back they are saved in the same folder under teachers'
name, ie JohnSmith.xls, AnnabelleGreen.xls etc. Their data is compiled from
the form in a single column in a hidden sheet called Data in each workbook.

I want to make a database workbook (in the same folder) which will take
information from all of these data sheets in each file and keep them together
on one sheet so that we can run mailmerges etc.

I could copy and paste the data, but I would like it to update
automatically. I would like them to be able to save their application form,
enter the name once on an index sheet, and the database would update with the
info from the new application. I would also like not to use macros as our
council computers crash a lot, if possible I would like to do this entirely
with formulas and links. (I realise this would be easy with a "find and
replace" macro).

I can link to any of the teacher applications once I know their name - with
='[JohnSmith.xls]Data'!B2 etc.

Basically I would like the "Johnsmith.xls" bit to be variable based on what
I type into a cell in an index of teachers, so that in the index I will have
cell a1 "johnsmith.xls", cell a2 "annabellegreen.xls", and in my database
sheet I will have "='[Index!a1]Data'!B2" which will point to cell b2 in
sheet "data" of workbook "Johnsmith.xls"
and
"='[Index!a2]Data'!B2" which will point to cell b2 in sheet "data" of
workbook "Annabellegreen.xls"

However I can't find a way of making the filename in a link variable based
on the cell value.


Is there a way of doing this? It feels like there should be, but I can't
find a way. I thought the "substitute" command might work but it seems it
only operates on the result of a formula and not on the actual text of the
formula itself.

Many thanks to anyone who can sort this out!

Bekki

Thank you, that is perfect and exactly what I was looking for.

I have to say I don't understand firewalls, i assumed it was some anti-virus
software that didn't allow scripts which interfered with anything VBa or
activex related we tried to do (ie assigning a macro to a button)and wouldn't
let us save it or crashed the application, but I could be wrong - our IT
department doesn't know why we can't use macros on our machines either!

Thank you again though, you have offered a very elegant solution to my messy
problem!

Bekki

"Bernie Deitrick" wrote:

Bekki,

The typical solution would be

=INDIRECT("'C:\Path\[" & A1 & "]Data'!$B$2")

with the filename in A1.

But that will not work unless the file named in A1 is open. All other solutions that I know of are
macro based.

Firewalls will ignore what is already behind them, so why won't macros work?

HTH,
Bernie
MS Excel MVP


"Bekki May" wrote in message
...
Thank you for your reply!

I was hoping to have them update automatically from the index - so that the
user, who will not be me but an HR person who has hundreds of forms to enter
and does not like excel much, can just write "John Smith.xls" in cell A1 of
the index sheet, "Anabelle Green.xls" in cell A2 etc, and then the database
sheet will link to the relevant columns in johnsmith.xls and
anabellegreen.xls, which I have previously set up to refer to cells A1 and A2
in the Index sheet... so that the end user only has to write the name of each
file he has saved once, in the index.. I like to make my spreadsheets so that
the user has to do as little as possible, so that there is less to break.

Is this not possible? It sounds from your reply as if it isn't and the only
option is to do a find and replace every time, or make a macro to do a find
and replace for him (which would be a perfect option if the council firewall
didn't appear to object to them). I remain hopeful that there is a way to put
a cell reference in a link and I am just not explaining it very well!

Thank you again for taking the time to reply to me,
Bekki


"Bernie Deitrick" wrote:

Bekki,

The easiest way to do this without using macros is to set up one set of formulas, using the form

='[JohnSmith.xls]Data'!$B$2

(with absolute cell addressing - the $s are needed for this)

and then copy all those formulas to as many rows as you need. Then use Find and Replace to
replace
JohnSmith with AnnabelleGreen, etc. in that one row, and so on adnaseum.

And that's why macros are handy....

HTH,
Bernie
MS Excel MVP


"Bekki May" Bekki wrote in message
...
Hello,
Please bear with me - I am a long term excel user but I am new to Questions
and I may not have phrased this very well.. I am using Excel 2000 on a work
computer. I apologise if this has already been answered - I did see some
similar questions but none which explained how to do this.

I have made an application form spreadsheet to send out to new teachers.
When they are sent back they are saved in the same folder under teachers'
name, ie JohnSmith.xls, AnnabelleGreen.xls etc. Their data is compiled from
the form in a single column in a hidden sheet called Data in each workbook.

I want to make a database workbook (in the same folder) which will take
information from all of these data sheets in each file and keep them together
on one sheet so that we can run mailmerges etc.

I could copy and paste the data, but I would like it to update
automatically. I would like them to be able to save their application form,
enter the name once on an index sheet, and the database would update with the
info from the new application. I would also like not to use macros as our
council computers crash a lot, if possible I would like to do this entirely
with formulas and links. (I realise this would be easy with a "find and
replace" macro).

I can link to any of the teacher applications once I know their name - with
='[JohnSmith.xls]Data'!B2 etc.

Basically I would like the "Johnsmith.xls" bit to be variable based on what
I type into a cell in an index of teachers, so that in the index I will have
cell a1 "johnsmith.xls", cell a2 "annabellegreen.xls", and in my database
sheet I will have "='[Index!a1]Data'!B2" which will point to cell b2 in
sheet "data" of workbook "Johnsmith.xls"
and
"='[Index!a2]Data'!B2" which will point to cell b2 in sheet "data" of
workbook "Annabellegreen.xls"

However I can't find a way of making the filename in a link variable based
on the cell value.


Is there a way of doing this? It feels like there should be, but I can't
find a way. I thought the "substitute" command might work but it seems it
only operates on the result of a formula and not on the actual text of the
formula itself.

Many thanks to anyone who can sort this out!

Bekki