Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Syntax for using variables in a cell formula to reference paths/fi
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 a '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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Syntax for using variables in a cell formula to reference paths/fi
You can use the INDIRECT() function, but the referenced workbooks would have
to be open. Jerry "colorado808" wrote: 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 a '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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Syntax for using variables in a cell formula to reference paths/fi
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. colorado808 wrote: 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 a '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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Syntax for using variables in a cell formula to reference path
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 "Dave Peterson" wrote: 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. colorado808 wrote: 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 a '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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Syntax for using variables in a cell formula to reference paths/fi
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???? "colorado808" wrote: 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 a '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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using variables in a cell/formula | Excel Worksheet Functions | |||
Syntax to return the value of a control by reference | Excel Discussion (Misc queries) | |||
Vary variables in a formula via reference to another cell | Excel Discussion (Misc queries) | |||
cell variables in IF formula | Excel Discussion (Misc queries) | |||
Range.Select Using Variables - need syntax | Excel Discussion (Misc queries) |