Adding and subtracting from a column.

[Eddie Wall]

I have a stock list with a "quantity" column.

What I would like to do is add the new stock to the existing quantity
column or subtract requisitioned items from the quantity column.

I guess it is a "self totaling" column I need to make.

As a separate exercise I would like to make a new column for orders.
Insert a quantity on that column for any of the items ( rows) I want
to order and then print it out... BUT I only want to print the rows
that have quantities in that column ( i.e. the actual items I am
ordering ). This column is not linked to the above column.

Could anyone point me in the right direction to achieve this.

Eddie
"But life is short and information endless;
nobody has time for everything" Aldous Huxley

 

[Don Guillett]

How about a nice defined name?
On the desired sheet>Edit>name>define>name it colB>in the refers to box type
in
=offset($B$1,1,0,counta($A:$A)-1,1)
now =sum(colb)

 

[Eddie Wall]

How about a nice defined name?
On the desired sheet>Edit>name>define>name it colB>in the refers to box type
in
=offset($B$1,1,0,counta($A:$A)-1,1)
now =sum(colb)

Thank you for the response...

Although that made no snese to me I appled it to the column I slected,
then to A1, B1 and no matter how or what column I applied it to I got
nothing....

At this point, it must be obvious that I am cluseless on Excel... what
I did is an idiots step by step.,.....

BTW I am using Excel2000 does that make any difference.? Also my
"name/define" was under Insert......

Eddie
"But life is short and information endless;
nobody has time for everything" Aldous Huxley

 

[Eddie Wall]

and my speel chekker is broek !

Apologies if my spelling atrocities made anyone ill.....

Eddie

Thank you for the response...

Although that made no snese to me I appled it to the column I slected,
then to A1, B1 and no matter how or what column I applied it to I got
nothing....

At this point, it must be obvious that I am cluseless on Excel... what
I did is an idiots step by step.,.....

BTW I am using Excel2000 does that make any difference.? Also my
"name/define" was under Insert......

Eddie
"But life is short and information endless;
nobody has time for everything" Aldous Huxley

"But life is short and information endless;
nobody has time for everything" Aldous Huxley

 

[Don Guillett]

This assumes that you have data in col A with at least as many rows as col B
for which you want a sum in col B.
You are correct, it is under insert. In the name box type in colb>in the
refers to box type in (or copy/paste this using ctrl C >ctrl V). Look in the
help index to see how the OFFSET function works.

=OFFSET(Sheet8!$B$1,1,0,COUNTA(Sheet8!$A:$A)-1,1)
or if you are on the desired sheet, excel will fill in the name for you
=OFFSET($B$1,1,0,COUNTA($A:$A)-1,1)
then in a cell such as C1, type in
=sum(colb)
will total col b regardless of the additions or subtractions.

 

[Eddie Wall]

Well did as instructed...

I inserted colb in column D. (=OFFSET($B$1,1,0,COUNTA($A:$A)-1,1))

I inserted =sum(colb) in cell E1

I put numbers into A1, b1, c1, and d1, and nothing happened to E1.

I know I am missing something big here., !

I looked at OFFSET....

"Returns a reference to a range that is a specified number of rows and
columns from a cell or range of cells. The reference that is returned
can be a single cell or a range of cells. You can specify the number
of rows and the number of columns to be returned.

Syntax

"

I was LOST totally on the first line.... that makes no sense to me
whatsoever... and thats the Help file !!

Am I too dumb to use this ?

Eddie

This assumes that you have data in col A with at least as many rows as col B
for which you want a sum in col B.
You are correct, it is under insert. In the name box type in colb>in the
refers to box type in (or copy/paste this using ctrl C >ctrl V). Look in the
help index to see how the OFFSET function works.

=OFFSET(Sheet8!$B$1,1,0,COUNTA(Sheet8!$A:$A)-1,1)
or if you are on the desired sheet, excel will fill in the name for you
=OFFSET($B$1,1,0,COUNTA($A:$A)-1,1)
then in a cell such as C1, type in
=sum(colb)
will total col b regardless of the additions or subtractions.

"But life is short and information endless;
nobody has time for everything" Aldous Huxley

 

[Don Guillett]

You missed following the instructions. You were to type in as instructed.
When all else fails, RTFI
I just sent you a sample workbook.