='S:\Tax\_private\DATA COLLECTION\ABC Tax
Pack\State\FY07\Received\'[Pack_1.xls]S-S-2'!$B$12

For future reference, this is a GREAT web site:
http://www.rondebruin.nl/ado.htm

Regards,
Ryan--

--
RyGuy


"KarenH" wrote:

I have 76 workbooks with multiple worksheets within. For two of these
worksheets, I need to combine the values of all the cells into a new
worksheet/workbook. Based on a previous question/response in here, I was
going to link to the cells of each of the workbooks and then do a sum within
the consolidated workbook. However, I'm having trouble with the link to
reference the cell in one of the supporting workbooks. For example, I have
the following:

='S:\Tax\_private\DATA COLLECTION\ABC Tax
Pack\State\FY07\Received\Pack_1.xls['S-S-2']'!B12

Pack_1.xls is the name of the workbook, S-S-2 is the name of the worksheet,
B12 is the cell I need to pull the value of

I'm sure there is something wrong in here, and I'm sure it relates to the
embedded spaces in the paths ("Data Collection", "ABC Tax Pack") and the
special characters (the hyphen) in the name of the worksheet. I do not have
the option of changing what they have used for these names to eliminate the
spaces. I've tried about a dozen different variations to try and isolate the
spaces/hyphen, but no luck.

Any suggestions? And does this seem to be the best way of combining all of
these? Thanks in advance.