tracking and charting production

[Guest]

I have created a workbook to track daily/monthly production and efficiency
data. I have each day of the month as a new worksheet and corresponding tab.
Now I want to total this information, hopefully in a line chart so we can
check if production falls off in a particular day. Is this possible? I know
how to SUM the figures and get a running total, but I want a day by day
summary to correspond with the different points on the chart. Any ideas?

 

[Del Cotter]

I have created a workbook to track daily/monthly production and efficiency
data. I have each day of the month as a new worksheet and corresponding tab.
Now I want to total this information, hopefully in a line chart so we can
check if production falls off in a particular day. Is this possible? I know
how to SUM the figures and get a running total, but I want a day by day
summary to correspond with the different points on the chart. Any ideas?

Create a new tab in your worksheet, and make a column of values, one
from each daily sheet. Either populate the column by hand day by day, or
use the INDIRECT() function to have each value in the column read from a
different sheet.

 

[Guest]

Thanks for the reply. I did search the forums and came across this
suggestion. However, I have not been able to get a formula to work as of
yet. I'll give specifics so maybe someone could produce the correct formula.

My tabs are labeled "09. (1)" ,"09. (2)", . . . .and so on

All sheets are identical, so one cell I want to trend over time is cell J62
on all sheets.

Does it make a difference if the value in cell J62 is calculated by a
formula?


Thanks for any suggestions.

 

[Jon Peltier]

Put tab names in A2:Ann, put the cell address in B1. In B2 put this formula:

=INDIRECT($A2&"!"&B$1)

or if there are spaces and punctuation in your tab names, use this:

=INDIRECT("'"$A2&"'!"&B$1)

so the generated string surrounds the tab name in single quotes. Copy this
formula into B3:Bnn, so your values are brought into this range.

- Jon

 

[Del Cotter]

Thanks for the reply. I did search the forums and came across this
suggestion. However, I have not been able to get a formula to work as of
yet. I'll give specifics so maybe someone could produce the correct formula.

My tabs are labeled "09. (1)" ,"09. (2)", . . . .and so on

It's a little trickier when your sheets have spaces in their tab names,
but you just have to be more careful to use single quotes if you're
referring to the sheets. Have you researched the use of the INDIRECT()
formula?

All sheets are identical, so one cell I want to trend over time is cell J62
on all sheets.

That's good, it means the only thing you are varying in your expression
is the sheet name.

Does it make a difference if the value in cell J62 is calculated by a
formula?

Not a bit of difference. Excel can refer to formulas that refer to
formulas that refer to formulas, all the way down if necessary. Really,
even a non-formula number is just a simple formula of the form "=2", for
example.

To return the contents of the first four sheets, enter the following
into the first three columns of your summary sheet:

''09. (1)' J62 =INDIRECT(A1&"!"&B1)
''09. (2)' J62 =INDIRECT(A2&"!"&B2)
''09. (3)' J62 =INDIRECT(A3&"!"&B3)
''09. (4)' J62 =INDIRECT(A4&"!"&B4)

Note the two single quotes at the beginning of the sheet name. The first
is to tell Excel this is not a formula, then the second is an actual
single quote. The result of typing these in is:

'09. (1)' J62 2
'09. (2)' J62 1
'09. (3)' J62 1
'09. (4)' J62 1

Assuming the contents of your sheets are 2, 1, 1, and 1.

Post a question to microsoft.public.excel.worksheet.functions if you're
still having trouble, and they'll help you.

 

[Guest]

Thanks! I got the formula to work. Now my life is going to be much easier
and I'll be able to create a powerful tool of record.