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.

='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.

It's easier if you open both workbooks, then type equal sign in the cell
where you want the link,
then click window and the other workbook's name, then just go to this
particular cell (B12) and select it then press enter. Now you can close the
source workbook and excel will put in the exact path for you


--


Regards,


Peo Sjoblom



"KarenH" wrote in message
...
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.