If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Need suggestions on layout to report/pivot
Does anyone have any thoughts on how to arrange this in Excel 2003:
I have a group of invoice numbers. I have columns of inforamtion relating to the invoice: inv #, total amount, inv date, etc. That is currently set up with the numbers in the first column and the rest of the information in the columns next to it. So, if I have 1000 invoices, I have 1000 rows of data. Now I need to make payments against those rows. Each invoice can have anywhere from 1-20 payments made against it. For these payments, I need to record the payment date, the # hrs being paid, the amount being paid, and the payment number (1-20). Does anyone have any thoughts on how to arrange the data without having 4 columns for each of the 20 payments totaling 80 columns? I need to be able to report and pivot off this data. Thanks for any suggestions. |
#2
|
|||
|
|||
Need suggestions on layout to report/pivot
Hi Nadine,
Does anyone have any thoughts on how to arrange this in Excel 2003: Some thoughts here http://edferrero.com/ExcelTutorials/...0/Default.aspx Ed Ferrero www.edferrero.com |
#4
|
|||
|
|||
Need suggestions on layout to report/pivot
Ed,
Thanks for the link. Unfortunately I didn't see a way to add 20+ pmt per invoice when my worksheet contains 1000+ lines of invoices. Thanks for the link, though. I'm going to browse it further. "Ed Ferrero" wrote: Hi Nadine, Does anyone have any thoughts on how to arrange this in Excel 2003: Some thoughts here http://edferrero.com/ExcelTutorials/...0/Default.aspx Ed Ferrero www.edferrero.com . |
#5
|
|||
|
|||
Need suggestions on layout to report/pivot
Billy,
Does your sheet have an idea of how to handle 20+ pmts per invoice when the worksheet has 1000+ rows of invoices? I know about named ranges but I don't see how that can help me log all the pmts without adding more rows or 60+ columns needed to record the date, the hours, and the payment. If your sheet has ideas on that, I'll send you my email address. Thanks so much. Nadine "Billy Liddel" wrote: Hi Nadine, I cobbled together a simple spreadsheet based on your post. It uses some of the techniques covered in Eds Tutorial (named ranges, and expanding formulas) e-mail me at and I'll post the file to you. Do the obvious with NOSPAM Regards Peter "Nadine" wrote: Does anyone have any thoughts on how to arrange this in Excel 2003: I have a group of invoice numbers. I have columns of inforamtion relating to the invoice: inv #, total amount, inv date, etc. That is currently set up with the numbers in the first column and the rest of the information in the columns next to it. So, if I have 1000 invoices, I have 1000 rows of data. Now I need to make payments against those rows. Each invoice can have anywhere from 1-20 payments made against it. For these payments, I need to record the payment date, the # hrs being paid, the amount being paid, and the payment number (1-20). Does anyone have any thoughts on how to arrange the data without having 4 columns for each of the 20 payments totaling 80 columns? I need to be able to report and pivot off this data. Thanks for any suggestions. |
#6
|
|||
|
|||
Need suggestions on layout to report/pivot
Hi Nadine
Did you see my second suggestion to you, where I was proposing that the Payments were recorded on a separate sheet. They could be entered on separate rows, or in successive columns along a single row. Then on your main sheet, have a SUMIF formula to give the single value of all payments made so far. As I said send me a copy of your workbook if you wish. roger at technology4u dot co dot uk -- Regards Roger Govier Nadine wrote: Ed, Thanks for the link. Unfortunately I didn't see a way to add 20+ pmt per invoice when my worksheet contains 1000+ lines of invoices. Thanks for the link, though. I'm going to browse it further. "Ed Ferrero" wrote: Hi Nadine, Does anyone have any thoughts on how to arrange this in Excel 2003: Some thoughts here http://edferrero.com/ExcelTutorials/...0/Default.aspx Ed Ferrero www.edferrero.com . |
Thread Tools | |
Display Modes | |
|
|