Converting a string into a filename

[Stanley_Ford]

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I have a workbook containing data pertaining to many separate weeks, months, quarters of data. Each week, month, and quarter is on a separate worksheet in the workbook. I'll refer to this as "Workbook A".

I have a worksheet (call it "Analysis&quot in another workbook (named "Workbook B&quot to which I want to add dynamic references to individual worksheets in Workbook A. For example, if I enter a week number such as "24" into Workbook B, I want it to use data from the worksheet named "Week24" in Workbook A to populate the rest of Analysis. Next, if I enter "20" into the same cell of Workbook A, I want it to pull data from "Week20" in Workbook A.

I can build a string which looks like a directory path and filename, with the square brackets, etc in all the right places, but Excel doesn't seem to recognize it as a string.

So, my question is: How do you convert a string into a filename for excel to go into a separate workbook/worksheet and return data?

 

[John_McGhie_[MVP]]

You need to feed the resulting string into a Formula, surrounded by quotes.

A "File Name" in Excel IS simply a string, but you need to use that string
in a formula that returns the data.

Cheers

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel
I have a workbook containing data pertaining to many separate weeks, months,
quarters of data. Each week, month, and quarter is on a separate worksheet in
the workbook. I'll refer to this as "Workbook A".

I have a worksheet (call it "Analysis") in another workbook (named "Workbook
B") to which I want to add dynamic references to individual worksheets in
Workbook A. For example, if I enter a week number such as "24" into Workbook
B, I want it to use data from the worksheet named "Week24" in Workbook A to
populate the rest of Analysis. Next, if I enter "20" into the same cell of
Workbook A, I want it to pull data from "Week20" in Workbook A.

I can build a string which looks like a directory path and filename, with the
square brackets, etc in all the right places, but Excel doesn't seem to
recognize it as a string.

So, my question is: How do you convert a string into a filename for excel to
go into a separate workbook/worksheet and return data?

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]