Syntax for using variables in a cell formula to reference paths/fi

[colorado808]

Ok, I have data in several other exel files that I want to link to in several
formulas throughout my spreadsheet.

For example the data in the other worksheets I linking to are:

'H:\Financials\2007\January\[Plant1 Statistics.xls]Worksheet1'!B$26
'H:\Financials\2007\January\[Plant2 Statistics.xls]Worksheet1'!B$26
'H:\Financials\2007\February\[Plant1 Statistics.xls]Worksheet1'!B$26
'H:\Financials\2007\February\[Plant2 Statistics.xls]Worksheet1'!B$26
'H:\Financials\2007\January\[Plant1 salespeople.xls]Worksheet1'!B$26
'H:\Financials\2007\January\[Plant1 salespeople.xls]Worksheet2'!B$26

I would like to calculate formulas using this other data, by being able to
use variables for different components of the path name for these files, so
that I can easily change what files the data is being pulled from to populate
a cell.

For example, I'd like to be able to let A1="January", B1="Statistics" and
C1="Worksheet", D1="H:\Finanacials\2007\". E1="Plant1

and then have my formula fill these "variables" into my cell's formula to
use the right path to pull the data in from the correct other worksheet.

So my cell formula(with the correct syntax) might read something like:
='D1\A1\[E1 B1]C1'!B26

to get the value in: H:\Financials\2007\January\[Plant1
salespeople.xls]Worksheet1'!B$26

This example is a little extreme, however I am trying to link to about 50
different worksheets that have consistent paths, formatted worksheet names
and formulas, and I am trying to find out the syntax to use variables within
these paths, rather than having to "hard-code" the different path names
through out my spreadsheet that is accumulating the data.

Any help you could give me would be GREATLY appreaciated!!!

Thanks,
Joan

 

[Jerry W. Lewis]

You can use the INDIRECT() function, but the referenced workbooks would have
to be open.

Jerry

 

[Dave Peterson]

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

Ok, I have data in several other exel files that I want to link to in several
formulas throughout my spreadsheet.

For example the data in the other worksheets I linking to are:

'H:\Financials\2007\January\[Plant1 Statistics.xls]Worksheet1'!B$26
'H:\Financials\2007\January\[Plant2 Statistics.xls]Worksheet1'!B$26
'H:\Financials\2007\February\[Plant1 Statistics.xls]Worksheet1'!B$26
'H:\Financials\2007\February\[Plant2 Statistics.xls]Worksheet1'!B$26
'H:\Financials\2007\January\[Plant1 salespeople.xls]Worksheet1'!B$26
'H:\Financials\2007\January\[Plant1 salespeople.xls]Worksheet2'!B$26

I would like to calculate formulas using this other data, by being able to
use variables for different components of the path name for these files, so
that I can easily change what files the data is being pulled from to populate
a cell.

For example, I'd like to be able to let A1="January", B1="Statistics" and
C1="Worksheet", D1="H:\Finanacials\2007\". E1="Plant1

and then have my formula fill these "variables" into my cell's formula to
use the right path to pull the data in from the correct other worksheet.

So my cell formula(with the correct syntax) might read something like:
='D1\A1\[E1 B1]C1'!B26

to get the value in: H:\Financials\2007\January\[Plant1
salespeople.xls]Worksheet1'!B$26

This example is a little extreme, however I am trying to link to about 50
different worksheets that have consistent paths, formatted worksheet names
and formulas, and I am trying to find out the syntax to use variables within
these paths, rather than having to "hard-code" the different path names
through out my spreadsheet that is accumulating the data.

Any help you could give me would be GREATLY appreaciated!!!

Thanks,
Joan

 

[colorado808]

Wow, Dave and Jerry!!! Thank you!

I've never used the indirect function before, and have been looking for
something that would do that for quite some time. Silly me didn't know of
the Communities where you could post questions, and so I have been at a loss!
I will be using this function a lot.

Dave, I also downloaded the Add-in you provided the link for! Terrific. It
works exactly as I need it to, so I don't have to go open all those files....
This will save sooooo much time, and hopefully make the tool that I am
designing for myself useful for other regional managers that I work with!!!
I was very excited when it also said you could imbed the add-in into the
workbook, this way I can share it with others and they can have the same
results.

Thank you so very very much! Hope that you both have a lovely week!

Sincerely,
Joan

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

Ok, I have data in several other exel files that I want to link to in several
formulas throughout my spreadsheet.

For example the data in the other worksheets I linking to are:

'H:\Financials\2007\January\[Plant1 Statistics.xls]Worksheet1'!B$26
'H:\Financials\2007\January\[Plant2 Statistics.xls]Worksheet1'!B$26
'H:\Financials\2007\February\[Plant1 Statistics.xls]Worksheet1'!B$26
'H:\Financials\2007\February\[Plant2 Statistics.xls]Worksheet1'!B$26
'H:\Financials\2007\January\[Plant1 salespeople.xls]Worksheet1'!B$26
'H:\Financials\2007\January\[Plant1 salespeople.xls]Worksheet2'!B$26

I would like to calculate formulas using this other data, by being able to
use variables for different components of the path name for these files, so
that I can easily change what files the data is being pulled from to populate
a cell.

For example, I'd like to be able to let A1="January", B1="Statistics" and
C1="Worksheet", D1="H:\Finanacials\2007\". E1="Plant1

and then have my formula fill these "variables" into my cell's formula to
use the right path to pull the data in from the correct other worksheet.

So my cell formula(with the correct syntax) might read something like:
='D1\A1\[E1 B1]C1'!B26

to get the value in: H:\Financials\2007\January\[Plant1
salespeople.xls]Worksheet1'!B$26

This example is a little extreme, however I am trying to link to about 50
different worksheets that have consistent paths, formatted worksheet names
and formulas, and I am trying to find out the syntax to use variables within
these paths, rather than having to "hard-code" the different path names
through out my spreadsheet that is accumulating the data.

Any help you could give me would be GREATLY appreaciated!!!

Thanks,
Joan

 

[colorado808]

I have one more question, with these functions, will the formulas constantly
be rechecking the source data, or does it pull it in once upon opening the
workbook or when I first enter the cell's formula in? Please let me know how
to control the updates,... I don't want to keep trying to access network
drives, but I want to ensure that it can go out and get updates when I need
it to????