On Thu, 13 Sep 2007, in microsoft.public.excel.charting,
JOE RR said:

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.

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.