How do I make a column equal a formula in Excel?
 

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?

How do I make a column equal a formula in Excel?
 

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?

How do I make a column equal a formula in Excel?
 

=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#

How do I make a column equal a formula in Excel?
 

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#

How do I make a column equal a formula in Excel?
 

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.

How do I make a column equal a formula in Excel?
 

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

How do I make a column equal a formula in Excel?
 

Although I appreciate the suggestion, I don't think putting the information
on a single worksheet will make the information easier to view and analyze
for our purposes, especially since the original artifact catalog sheet is 189
lines long. It would be much simpler for everyone if a single worksheet
could equal a bag, because if we try to stick all the FS#'s on one page, the
information actually would become unwieldy and we would lose the ability to
quickly switch to a page that we might have corrections to enter on.

Here is the actual set up of the catalog form if it helps anybody figure out
how to set the column as a formula (note that weight is in g.)

Artifact Count
Weight Notes
Kitchenwares

Ceramics
Stoneware
Ginger Beer Bottle 1
3.3 mouth of vessel
Basaltware 1
1.1 leaf pattern
Earthenware
Pearlware, plain 10
10.5 one handle
Glass
Bottle Glass, Color #4 3
5.6 pontil mark
.....etc., there actually many, many more per each category and more
categories.

How do I make a column equal a formula in Excel?
 

Hi archllama,

It is really irrelevant how long your original catalogue sheet is. The
bigger the database, the better the single datasheet structure works
over alternatives. Yours is tiny. I'm not sure you understand that
you CAN very simply see, and adjust, everything relating to a single
bag on a single sheet when you use the single datasheet structure - e.g
through filter drop down boxes. Everything else DOES become invisible.
Virtually no time to set this up. And you CAN "quickly switch to a page
that we might have corrections to enter on" by simply changing your
filter selection from the drop-down box.

Good luck whichever way you choose.


archllama Wrote:
Although I appreciate the suggestion, I don't think putting the
information on a single worksheet will make the information easier to
view and analyzefor our purposes, especially since the original arti
act catalog sheet is 189 lines long. It would be much simpler for
everyone if a single worksheet could equal a bag, because if we try to
stick all the FS#'s on one page, the information actually would become
unwieldy and we would lose the ability to quickly switch to a page that
we might have corrections to enter on.



--
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