how to reference different workbooks/sheets in formula?

[Steve]

i have 30 different workbooks,
each workbook has >20 individual sheets.
i have a list with two columns, one is the name of the
workbooks, and one is the name of each sheets.
here is what i want to do.
i want to do a count on 'column z' on every sheets.
(column z on every sheets has n number of data, i want to
count how many.)

ie,

column a column b column c
(sheets) (workbooks) (number of data point)
-------- ----------- ----------------------
g-1 a.xls =count('[a.xls]g-1'!$z:$z)
g-2 a.xls =count('[a.xls]g-2'!$z:$z)
g-3 a.xls =count('[a.xls]g-3'!$z:$z)
g-4 b.xls =count('[b.xls]g-4'!$z:$z)
g-5 b.xls =count('[b.xls]g-5'!$z:$z)
..
..
..

since, i have 30 workworks and each has more than 20
sheets.
the above list will have > 600 rows.
is there a easy way to put a formula in 'column c' above,
so that it will use 'column a' and 'column b' as references
and i don't have to manually type in that formula
in 'column c' >600 times.

thx in advance.

 

[Dave Peterson]

First, if those 30 different workbooks are closed, you're going to need the path
of the workbook in your formula. And it becomes a tiny bit more than just
creating the formula:

Kind of:
=COUNT('C:\My Documents\excel\[a.xls]g-1'!$Z:$Z)

I could build a string that looked like that formula with this:

="=count('c:\my documents\excel\["&B1&"]"&A1&"'!$z:$z)"

You could put the path in the formula or refer to in in another cell--say D1
contained:
c:\my documents\excel, then my formula that builds the string is:

="=count('"&$D$1&"\[" &B1&"]"&A1&"'!$z:$z)"
(watchout for the trailing backslash)

Now after you've verified that the formula is ok, you can select that range of
formulas and convert them to values: Edit|copy, Edit|paste special|values

Now, you have strings that look exactly like your formulas, but they're not
formulas, yet!

Select that range and
edit|replace
= with =
This'll make excel reevaluate the strings as formulas.

I'd do it with a couple of cells first. If you screw up, you might be pointing
at a worksheet/workbook that doesn't exist and you'll be prompted to fix it. If
you did all 600 at once (incorrectly), it'll get really boring dismissing those
dialogs.

i have 30 different workbooks,
each workbook has >20 individual sheets.
i have a list with two columns, one is the name of the
workbooks, and one is the name of each sheets.
here is what i want to do.
i want to do a count on 'column z' on every sheets.
(column z on every sheets has n number of data, i want to
count how many.)

ie,

column a column b column c
(sheets) (workbooks) (number of data point)
-------- ----------- ----------------------
g-1 a.xls =count('[a.xls]g-1'!$z:$z)
g-2 a.xls =count('[a.xls]g-2'!$z:$z)
g-3 a.xls =count('[a.xls]g-3'!$z:$z)
g-4 b.xls =count('[b.xls]g-4'!$z:$z)
g-5 b.xls =count('[b.xls]g-5'!$z:$z)
.
.
.

since, i have 30 workworks and each has more than 20
sheets.
the above list will have > 600 rows.
is there a easy way to put a formula in 'column c' above,
so that it will use 'column a' and 'column b' as references
and i don't have to manually type in that formula
in 'column c' >600 times.

thx in advance.