Hi

Define a named range
InsertNameDefine. Name MyRng Refers to =
OFFSET($AE$2,0,0,COUNTA($AE:$AE))

=SUMPRODUCT(N(INDIRECT("'"&MyRng&"'!"&ADDRESS(ROW( );COLUMN()))))


--
Regards
Roger Govier



wrote in message
ups.com...
Great, this is just what I needed. I just changed the formula so I
could use it in any cell. So now it is:

=SUMPRODUCT(N(INDIRECT("'"&AE2:AE30&"'!"&ADDRESS(R OW();COLUMN()))))

Thanks a lot man, this will be a huge time save for me!

There's just another small problem. As I will have many summary sheets
where many cells will be using this formula, it would be great if I
could somehow make one "master cell" in a sheet where I could make the
changes to the formula(to define the range for sheet names and avoid
the #REF error), which would then be applied to all cells using the
formula.

On Oct 11, 4:36 pm, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk
wrote:
Hi

Then try
=SUMPRODUCT(N(INDIRECT("'"&AE2:AE30&"'!B1")))

You need to ensure that the range AE2:AE30 matches the range that
contains
Sheet names.
If there are any blank cells in that range you will get a #REF error.
--
Regards
Roger Govier

wrote in message

oups.com...

Thanks for your answer Roger. I knew about this formula, but still I
would need the formula to sum according to provided list of data entry
sheets. The formula I wrote in my second post works perfectly, the
only problem is that it's going to be too long to enter if I make it
for 30 sheets.

On Oct 11, 12:20 pm, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk
wrote:
Hi

You can use something like
=SUM(Sheet1:Sheet30!A1)

OR, what I prefer, (and it isn't dependent upon having sheets named 1
to
30), you can insert 2 dummy sheets called First and Last.
Drag these so that they encompass your sheets to be summed, in a sort
of
"sandwich", with your summary sheet outside of this range. Then
=SUM(First:Last!A1)

--
Regards
Roger Govier

wrote in message

groups.com...

Hi everybody,

I'm having a little problem with my excel table.

I have many sheets for entering data, they look exactly the same,
just
the sheet name is different. Then I also have the summary sheets,
that
are also the same as the one for data entry, just they contain
formulas to sum data from data entry sheets.

So what I'm trying to make a list of data entry sheets in the
summary
sheet and then the formula that would sum all the data entered in
the
listed sheets in the same cell address where the formula is located.

The list of sheets to sum could contain let's say up to 30 places.

And another quick question. Is it possible to somehow link make some
kind of "master formula" and then use it in multiple cells, so when
you change the master formula all the cells that use it change too.

Thanks.