Working w/totals in sorted/subtotaled spreadsheet

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

BratDawg

Carla
Thread Starter
Joined
Sep 23, 2004
Messages
677
I have a spreadsheet where I have sorted and created subtotals. I want to roll up the totals so I only see the name and total then I want to work with those totals. Usually I'm trying to figure out the difference between what my accounting software is showing and a vendor invoice or something like that. So I'm cobbling information together and then trying to run a difference column.

If I'm working with a rolled up spreadsheet like this, tho, and I copy a formula down in the difference column (like column b - c) I get bad numbers because it's not just looking at my subtotal lines, it's looking at ALL the lines.

I tried copying and pasting values into another worksheet but that didn't seem to work either. Suggestions?

Attaching a sample so you can see what I'm talking about.

Thanks for any suggestions. Our accounting software is designed so we have to rely extensively on excel exports for most reconciliation type tasks. :(
 

Attachments

Joined
Jun 8, 2001
Messages
2,583
Not sure if this what your looking for but something like this might work.
=IF(ISBLANK(D1),SUM(B1,-E1),"0")

Saying if D1 is blank then SUM B1 and negative E1...Else its 0.
You can pull this formula down and it will sum lines where the deduction type is blank.
I did a SUM on D4 in the example.
 

BratDawg

Carla
Thread Starter
Joined
Sep 23, 2004
Messages
677
Thanks but when I tried to download it again to see what you did, it wouldn't open anything and said the file was corrupt. I tried pasting that formula in column g and got funny results.

What I want to do is take raw export, sort and subtotal - in this case column B exported from my software. I then got an export of an AFLAC invoice and sorted by name also and plugged those totals in E. Then I wanted to subtract E from B to determine the difference between what was withheld and remitted from employee checks to what was invoiced by the insurance company. I only wanted to focus on the totals, not the individual transactions (expanded).

I ended up, to get what I needed, copying the formula in F4 and pasting it in column F on each total line, as opposed to just copying down the formula. I'm trying to figure out a faster way. We have 140 employees and alot of them have aflac. :(
 
Joined
Jun 8, 2001
Messages
2,583
I didn't update your workbook but I was able to pull it down and edit with no problems.
When I copied the formula from TSG and pasted it made merged cells, which screwed things up. Maybe if you copy form a 'Code' box it will paste clean? If not simply got to 'format cell' - alignment and click the merge cells box till its empty.

Code:
=IF(ISBLANK(D1),SUM(B1,-E1),"0")
This formula will allow you to just pull down instead of pasting each time. You will be able to focus on the difference because it will display a "0" if D1 is blank. You could change the "0" to anything you want.
 
Joined
Jun 8, 2001
Messages
2,583
Strange but true. If I do "CNTL V" or Paste it makes a merged cell(When copied from TSG thread). Paste special "match desitnation format" paste correctly.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top