Invoicing

R

Rob #4

Board Regular
Joined
Jun 19, 2003
Messages
194
I am trying to figure out a way to sort this information by Description, so that I can compare Rates easily. However as time goes on I will need to add more invoices to this worksheet and will need those items added to the summary also. I am trying to do the summary on another worksheet. Any ideas?
National Waterworks Invoice Summary.xls
ABCDEF
23DateInvoice #DescriptionQtyRateTotal
2412/5/2005301146116 TJ CL52 DI PIPE C/L108$33.48$3,615.84
2512/5/2005301146116 EJIW GATE VLV RW OR1$3,355.00$3,355.00
2612/5/200530114615668S 2 CI CLEVE VLV BOX COMP1$75.00$75.00
2712/5/2005301146116 MJ 45 BEND C/L CP DI C1531$355.00$355.00
2812/5/2005301146116 MJ 22-1/2 BEND C/L CP DI C1532$354.50$709.00
2912/5/2005301146116 MJ 11-1/4 BEND C/L CP DI C1531$356.00$356.00
3012/5/2005301146116 MJ L/P SLEEVE CP DI C1531$287.50$287.50
3112/5/2005301146116 MJ ACC SET L/ GLAND12$0.00$0.00
3212/5/2005301146116 FIELD LOCK GASKET3$241.05$723.15
33Sales Tax$710.74
34Total$10,187.23
35
3612/8/2005303550312 ONE-LOK DI RESTRAINT SLD128$48.00$384.00
3712/8/2005303550312 MJ ACC SET L/ GLAND8$0.00$0.00
3812/8/2005303550312 MJ L/P SLEEVE CP DI C1534$113.19$452.76
39Sales Tax$52.30
40Total$889.06
41
4212/12/2005303994712 ONE-LOK DI RESTRAINT SLD121$48.00$48.00
4312/12/2005303994712 MJ ACC SET L/ GLAND2$0.00$0.00
4412/12/2005303994712 MJ CAP T/C CP DI C1531$59.78$59.78
4512/12/2005303994712 MJ PLUG T/C CP DI C1531$60.76$60.76
46Sales Tax$12.64
47Total$181.18
IX Invoice Data
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
It'll be a lot easier if you remove the Sales Tax, Total & blank rows and keep a continuous running summary. You can always SUMIF on Invoice #.

Smitty
 
Upvote 0
I don't need the tax and total to have anything to do with the summary. Could I just hide those or something when I go to do some type of sorting?
 
Upvote 0
Could I just hide those or something when I go to do some type of sorting?
Click to expand...

Not really, Excel is going to see those as non-contiguous ranges and won't sort them together.

Smitty
 
Upvote 0
OK so If I got rid of those rows what would I need to do?
Click to expand...

If you get rid of those, then you can sort with no problem.

Is the sheet that you posted your actual customer invoice?

A common equest here is to have an invocie template that can build a record of previous invoices (using VBA).

I can send you a copy if you want.

Smitty
 
Upvote 0
Yes please send me that so I can take a look at it.

However, regarding my previous question I am trying to do a summary on a separate worksheet. I don't want to just sort them.
 
Upvote 0
Sent it, but first, edit your post and delete your e-mail address. It's not secure in a public forum (read SPAM).

How do you populate the invocie? How do you want to transfer the data to a summary sheet.

What you originally posted looks like a running summary as it is, so I guess that's where I'm a bit confused.

Smitty
 
Upvote 0
I want to take the individual items, listed under the description heading, and put them on another worksheet sorted by like description. This isn't the complete list of invoices so it looks like there aren't very many duplicates, but in actuality There are like 100 invoices with many like decriptions.
 
Upvote 0
You can use a Pivot Table report. This will give you a fully automatic and dynamically updated summary for comparison.

Place descriptions to rows and rates to columns. The data field should be "Sum of Rate". Since the combination of Description + Rate is unique in each invoice the "Sum of Rate" will give you the rate itself.

If you select the data range A:F for your pivot table, once you add a new invoice to your file and then switch to pivot table and click "Refresh", it will update the report taking into account this invoice.

I sent you an example by mail.

Enjoy.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Sum Data Across Columns and Rows between Dates
2
Replies
19
Views
272
awoohaw
awoohaw
R
  • Question
Match multiple payments to Invoices for calculating overdue charges
Replies
1
Views
268
OaklandJim
O
P
  • Solved
How to copy and paste the next row VBA Help.
Replies
8
Views
352
James006
James006
P
  • Question
How to copy and paste the next row VBA Help.
Replies
4
Views
337
punnipah
P
D
  • Question
86 Seconds to run my bit of Code to delete blank rows ! Would like it to run faster, learning
Replies
4
Views
308
Ottsel
Ottsel

Forum statistics

Threads
1,215,052
Messages
6,122,878
Members
449,097
Latest member
dbomb1414

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top