I am an archaeology student and I am trying to make a catalog workbook for
artifacts. We have the artifacts cataloged by bag number and FS#. What I have
done is entered the FS# and bag number as names of the worksheets and then I
total the worksheet on as FS#-T page. However, occasionally we encounter a
new type of artifact and have to add a new row for the artifact. Thus, we
select all the sheets and add a new row to the catalog form. However, this
affects the formula sheet, and the summation formula does not appear in the
new row. Is there a way to make the column that is adding information for the
separate worksheets equal the formula so that when we insert a new row for a
new artifact, we do not have to go back and copy and paste the formula. Right
now, it is not problematic, but in the summer I will be dealing with a large
number of worksheets.

Also, I noticed that the sum function has a limit of 30 numbers. I know that
at some point I may exceed this in my summations, particularly when I create
a page that totals the results from all the FS#'s (we currently have over 500
FS#'s). Do y'all have any suggestions on how to deal with this?

Post your formula.

--
Don Guillett
SalesAid Software

"archllama" wrote in message
...
I am an archaeology student and I am trying to make a catalog workbook for
artifacts. We have the artifacts cataloged by bag number and FS#. What I
have
done is entered the FS# and bag number as names of the worksheets and then
I
total the worksheet on as FS#-T page. However, occasionally we encounter a
new type of artifact and have to add a new row for the artifact. Thus, we
select all the sheets and add a new row to the catalog form. However, this
affects the formula sheet, and the summation formula does not appear in
the
new row. Is there a way to make the column that is adding information for
the
separate worksheets equal the formula so that when we insert a new row for
a
new artifact, we do not have to go back and copy and paste the formula.
Right
now, it is not problematic, but in the summer I will be dealing with a
large
number of worksheets.

Also, I noticed that the sum function has a limit of 30 numbers. I know
that
at some point I may exceed this in my summations, particularly when I
create
a page that totals the results from all the FS#'s (we currently have over
500
FS#'s). Do y'all have any suggestions on how to deal with this?

=SUM('505-04-1'!H4, '505-06-1'!H4,'505-06-2'!H4, '505-04-1b'!H4)

As I drag it down, the row # changes to match the correct row on each page.
This is the first row that contains true calculations on the 505-T page, but
I am willing to sacrifice the column title if it will prevent me from having
to go back and copy formulas on the FS#-T pages when I move rows. Note: the
above formula changes from one FS#-T page to the next because they use the
sheets for that FS# so the worksheet names are slightly different. The sheet
name for a bag, as shown above, is broken down as FS#-year-bag#

You can create a formula that will total the same cell on *all* the sheets
of the same WB.

This SUM() formula is determined by the actual *physical* location of the
sheets within the WB.
It will total all sheets that are (sandwiched) between the sheets entered in
the formula.

This would add H4 on all the sheets in your example, as long as they were
physically between the ones in the formula:

=SUM('505-04-1:505-04-1b'!H4)

NOW ... you could add a new, blank sheet in front of your first sheet and
another after the last sheet and name them "Start" and "End", and your
formula would be:

=SUM(Start:End!H4)

You would then simply add all new sheets anywhere in between them (Start -
End), and your totals would be returned.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"archllama" wrote in message
...
=SUM('505-04-1'!H4, '505-06-1'!H4,'505-06-2'!H4, '505-04-1b'!H4)

As I drag it down, the row # changes to match the correct row on each
page.
This is the first row that contains true calculations on the 505-T page,
but
I am willing to sacrifice the column title if it will prevent me from
having
to go back and copy formulas on the FS#-T pages when I move rows. Note:
the
above formula changes from one FS#-T page to the next because they use the
sheets for that FS# so the worksheet names are slightly different. The
sheet
name for a bag, as shown above, is broken down as FS#-year-bag#

That simplifies the formula, but it does not help with the main question,
which is whether or not there is a way to make a column equal a formula so
that if I insert a new row, meaning a new artifact, to the catalog sheet, I
won't have to go back and copy and paste the formula. Eventually, there will
be several sheets with formulas, and it will be too time consuming to go back
and copy and paste formulas in the new rows.

Hi archllama,

Based on your description of the problem, I'd suggest that you strongly
consider changing your workbook structure (simple), rather than
searching for a way to attach formulae to columns (complex).

You will eliminate most maintenance problems and give yourself far more
flexibility in reporting and analysis if you collect all of your data on
a single worksheet (in a structured database format) and do all of your
reporting on a different worksheet or worksheets.

Using single word headings and a structured layout similar to below,
you'll be able to have all of your data on a single worksheet. You can
use filters to see/amend/add to a subsection of your data. When your
data is in this format, there is a multitude of simple formulae,
pivottables, etc to analyse your data & make your sums in your
reports.

FS_No Year Bag_No Count
505 04 1 4
505 04 1b 3
506 04 1b 3
506 05 1 5
507 04 1b 6

Cheers,


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=531346