Need to sumarize the count of materials on a Summary sheet

[ohmedic88]

I have a summary Sheet in a workbook and I want to summarize the count of materials on each sheet.

How can I write a formula that will use the Sheet name in Column C and return a count of materials in Column D.

[TABLE="width: 500"]
<tbody>[TR]
[TD]C Column[/TD]
[TD]D Column[/TD]
[/TR]
[TR]
[TD]Sheet Name[/TD]
[TD]Count of Materials[/TD]
[/TR]
[TR]
[TD]sheet 1[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]sheet 2[/TD]
[TD]25[/TD]
[/TR]
</tbody>[/TABLE]

Any assistance would greatly be appreciated... or links to any training that might assist.

Eric Mathewson

 

[Roxxien]

If you stay in this same workbook, Indirect can be a good idea for this.

=INDIRECT(string)

Your "string" is what you want to be converted in a function.

If you do =INDIRECT("'" & C2 & "'!A1) in your example, it will act as if you wrote ='sheet 1'!A1

 

[MrKowz]

Where in each sheet will we find the count of materials we want returned? I.e. where in sheet 1 do we get 10? Where in sheet 2 do we get 25?

 

[ohmedic88]

If you stay in this same workbook, Indirect can be a good idea for this.

=INDIRECT(string)

Your "string" is what you want to be converted in a function.

If you do =INDIRECT("'" & C2 & "'!A1) in your example, it will act as if you wrote ='sheet 1'!A1

I'm getting an error on this one but I need to do a range B7:B100 or some kind of dynamic range within that area.

Eric

 

[MrKowz]

I'm getting an error on this one but I need to do a range B7:B100 or some kind of dynamic range within that area.

Eric

Can you give an example of the data which is in B7:B100? Will it be consistent that the values you want returned/summarized will be in column B on each worksheet?

 

[Roxxien]

you need to do a range B7:B100, what does you mean by that?

 

[ohmedic88]

Can you give an example of the data which is in B7:B100? Will it be consistent that the values you want returned/summarized will be in column B on each worksheet?

This is my actual formula COUNT('99675868_rev_0011501262082957'!B7:B100) but I want to avoid writing each formula independently because C Column has my sheet names.

Does this help?

 

[MrKowz]

Assuming your sheet names begin in C1, try

=COUNT(INDIRECT("'"&C1&"'!B7:B100"))

 

[ohmedic88]

Awesome that works great...

Much appreciative

Eric

 

[MrKowz]

Awesome that works great...

Much appreciative

Eric

Happy to help - thanks for the feedback. Have a great day/weekend!