Formula is too long, (error message)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to sum numerous worksheets into a different workbook summary
sheet, however each time I select a cell within in the workbook it included
the workbook name and then the name of the worksheet and then the cell.
Is there a way that the workbook can be noted at the beginning of the
formula and then the rest of the formula will be the addition of each
worksheet?
eg. =sum('[Tracking revision2.xls]Sprinkler Fitters'!$C$6, '[Tracking
revision2.xls]Scaffolder!$C$6)
and so on forth. I would like to make it so the workbook is only mentioned
once at the beginning to shorten the formula.
Thanks
 
Hi

Could you not do all of your summation inside [Tracking Revision2.xls],
then just pull that Sum across to your other workbook?

If you inserted 2 dummy sheets called First and Last and positioned them
so that they sandwiched 'Sprinkler Fitter', 'Scaffolder' etc. then you
could use a Summary sheet outside the "sandwich" with formulae like
=SUM(First:Last!C6)

Pick up these values to take to your other workbook.
 
Maybe...

If you used another cell and =indirect().

But then the formula would break when that "sending" workbook was closed.

How about just putting formulas that point at the individual cells on another
sheet (hidden).

Then you could use:
=sum(sheet99!a1:a10)
or even put the sum in one of those cells and just retrieve that:
=sheet99!a1


I am trying to sum numerous worksheets into a different workbook summary
sheet, however each time I select a cell within in the workbook it included
the workbook name and then the name of the worksheet and then the cell.
Is there a way that the workbook can be noted at the beginning of the
formula and then the rest of the formula will be the addition of each
worksheet?
eg. =sum('[Tracking revision2.xls]Sprinkler Fitters'!$C$6, '[Tracking
revision2.xls]Scaffolder!$C$6)
and so on forth. I would like to make it so the workbook is only mentioned
once at the beginning to shorten the formula.
Thanks
Click to expand...
 
Thanks, I will try this and let you know if it worked.
Tanda

Roger Govier said:
Hi

Could you not do all of your summation inside [Tracking Revision2.xls],
then just pull that Sum across to your other workbook?

If you inserted 2 dummy sheets called First and Last and positioned them
so that they sandwiched 'Sprinkler Fitter', 'Scaffolder' etc. then you
could use a Summary sheet outside the "sandwich" with formulae like
=SUM(First:Last!C6)

Pick up these values to take to your other workbook.
--
Regards

Roger Govier


Tanda said:
I am trying to sum numerous worksheets into a different workbook
summary
sheet, however each time I select a cell within in the workbook it
included
the workbook name and then the name of the worksheet and then the
cell.
Is there a way that the workbook can be noted at the beginning of the
formula and then the rest of the formula will be the addition of each
worksheet?
eg. =sum('[Tracking revision2.xls]Sprinkler Fitters'!$C$6, '[Tracking
revision2.xls]Scaffolder!$C$6)
and so on forth. I would like to make it so the workbook is only
mentioned
once at the beginning to shorten the formula.
Thanks
Click to expand...
Click to expand...
 
Back
Top