Urgent! I am in dire trouble - boss says it can be done - How?

[Cathi]

I am need to create a sheet on which I can track 2 different totals in the
same column.

I need to be able to put multiple entries in and add them up as a sub total
of the new entries and then an overall total of all entries.
Ex:

Column A is a date column and column B is an inventory rec'v count

So C3=the subtotal of the new entries and C1= the overall total.

Is this even possible?

Any suggestions? I'm desperate as my boss is convinced that this is possible
in Excel.

I can't even seem to make my cell references absolute ...

I can't get the formula =$C$4 to point to an absolute cell reference so that
I can delete row 4 and the following row is brought up, now becoming row 4
and have the formula recognize it as such, I get #REF!

Again my boss is convinced that one is able to delete the referenced row and
it should just automatically update.

Am I missing something really blatant?

HELP!!

 

[Chip Pearson]

For the referencing #REF problem, use INDIRECT. E.g.,

=INDIRECT("A1")

will refer to cell A1 regardless of whatever rows you may insert or delete.
This is because Excel sees the "A1" as a simple character string, not an
actual cell reference, so it will not modify the text string as you
insert/delete rows/columns.

I'm not clear what you're looking for in the subtotals question.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Cathi]

Thank You, Thank you the Indirect worked like a dream~

The subtotals question:

if I am inserting several entries in a given week, they number of entries
changes every week so I can't just use a SUM function as I need to track only
those entries, how is it possible to both total "only the new weeks entries"
and then in the same column be able to total the whole column for an accurate
current inventory?

Is it possible, if not do you have any other suggestions?

 

[Pete_UK]

You can probably use SUMIF to get your subtotal, by comparing the
dates against another date (TODAY ?) which determines what is new. Put
your reference date in D1, then put this in C3:

=SUMIF(A1:A100,">="&D1,B1:B100)

I've assumed 100 entries - adjust to suit. This looks at your dates in
column A, compares them with the date in D1, and adds up column B
entries where the date is later than or equal to the date in D1.

Hope this helps.

Pete

 

[Cathi]

Like a dream, a few adjustments for cell refernces and ta da!!!

I cannot thank you enough , I've spent too much time trying to figure this
out and was thinking way way too hard.

My humble gratitude

 

[Pete_UK]

Thanks for feeding back Cathi - I'm glad you were able to get it to
work.

Pete