Variable reference in formula

keycapmgt · May 20th, 2002, 01:09 PM

I'm using a single worksheet to analyze data in various .XLS files. I would like to be able to enter the name of the .xls file I want to analyze in one cell of the worksheet and then reference that cell in the various formulas. As an example I would like a formula to count the number of items in an .xls file: =COUNT([NameOfFile.xls]NameOfWorksheet$A:$A). Instead of the specific name of the file I would like NameOfFile to be a variable.

Thanks

IML · May 20th, 2002, 01:16 PM

If the workbooks you are going to analyze will be open, you could use the indirect function for this.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 20th, 2002, 01:09 PM	#1
keycapmgt New Member Join Date: May 2002 Posts: 1	I'm using a single worksheet to analyze data in various .XLS files. I would like to be able to enter the name of the .xls file I want to analyze in one cell of the worksheet and then reference that cell in the various formulas. As an example I would like a formula to count the number of items in an .xls file: =COUNT([NameOfFile.xls]NameOfWorksheet$A:$A). Instead of the specific name of the file I would like NameOfFile to be a variable. Thanks

May 20th, 2002, 01:16 PM	#2
IML MrExcel MVP Join Date: Feb 2002 Location: Denver, CO Posts: 1,744	If the workbooks you are going to analyze will be open, you could use the indirect function for this.