indirect with wildcards
 

i have multiple worksheets. i want to sum the cell $I$34 from each workbook
into a single cell in a collation worksheet. is there any way i can use
indirect with a wild card?

sam

indirect with wildcards
 

Sam

To sum a cell from Sheet1,2,3,....Sheet10

=Sum(Sheet1:Shee10!$I$34)

If this post helps click Yes
---------------
Jacob Skaria


"sam" wrote:

i have multiple worksheets. i want to sum the cell $I$34 from each workbook
into a single cell in a collation worksheet. is there any way i can use
indirect with a wild card?

sam

indirect with wildcards
 

Hi,

Suppose the sheets are adjacent with the collation sheet at one end or the
other, then on the collation sheet type
=SUM(
click on the tab of the first sheet you want to sum
click on cell I34 of that sheet
hold down the Shift key and click on the last sheet of the collection and
press enter.

This method is safer than typing because there are a number of times when
you need to add single quotes if you type but Excel will build the formula
for you with point and click.

1. There must be no sheets between the first and last sheet which you don't
want summed with the approach.
2. If the sheets are non-adjacent then instead of Shift+clicking each tab
Ctrl+click them.
3. INDIRECT and wildcards are not needed for the example you gave although
you could us INDIRECT.



--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"sam" wrote:

i have multiple worksheets. i want to sum the cell $I$34 from each workbook
into a single cell in a collation worksheet. is there any way i can use
indirect with a wild card?

sam

indirect with wildcards
 

thankyou. worked just fine

"Shane Devenshire" wrote:

Hi,

Suppose the sheets are adjacent with the collation sheet at one end or the
other, then on the collation sheet type
=SUM(
click on the tab of the first sheet you want to sum
click on cell I34 of that sheet
hold down the Shift key and click on the last sheet of the collection and
press enter.

This method is safer than typing because there are a number of times when
you need to add single quotes if you type but Excel will build the formula
for you with point and click.

1. There must be no sheets between the first and last sheet which you don't
want summed with the approach.
2. If the sheets are non-adjacent then instead of Shift+clicking each tab
Ctrl+click them.
3. INDIRECT and wildcards are not needed for the example you gave although
you could us INDIRECT.



--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"sam" wrote:

i have multiple worksheets. i want to sum the cell $I$34 from each workbook
into a single cell in a collation worksheet. is there any way i can use
indirect with a wild card?

sam