Help with referencing multiple sheets in formulas

[Trev]

Greetings Xcellers

I have a workbook with 32 sheets, The first sheet is a month summary
and
the other 31 sheets are for each day of the month.

The sheets are names month,1,2,3..31

I need to copy information from the daily sheets to create a summary
on
the month sheet like follows
A B
1 Day Turnover .....
2 1 ='1'!E7
3 2 ='2'!E7
4 3 ...
5 5 ='5'!E7

Is there a way to substitute the sheet name from the column Day
instead of
manually having to enter each formula. There will be a number of
columns
and I have a number of similar workbooks to make.

IE for B2 a formula like =Sheet(a2)!$E$7

Then I can do one formula and just copy it down.

I am guessing it would take a user defined function. Being fairly new
to
those if you could point me in the right direction I would be most
appreciative

Thanks

Trev

ps Apologies if this message show up twice - I waited more than 24
hours and it was not at Google so I psted again from Google.

 

[ianripping]

Hi trev, try this:

=INDIRECT("'"&ROW()&"'!E7"

 

[Guest]

Or, a little more simply

=INDIRECT(A2&"!E7"

 

[Trev]

Or, a little more simply:

=INDIRECT(A2&"!E7")

Thanks ianripping and Eric

I used Erics simplifies formula and it works like a dream. After
reading the help files I can also understand what that function does.

However I do not think I would have found it by searching the help.
These groups are invaluable whe it comes to this sort of thing as even
the best search engine parser sometimes can't gve decent reulsts based
on ones often inaccurate search terms

Thanks again

PS I am actually using OOo Calc but when it comes to formulas and
built in fucntions it is so similar to Excel.

When it comes to Macros and UDFs however then Excel stuff ceases to be
as useful as the BASCI scripting language while very similar to VBA
has different objects and property names.


Trev