A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Need suggestions on layout to report/pivot



 
 
Thread Tools Display Modes
  #1  
Old May 7th, 2010, 11:03 PM posted to microsoft.public.excel.misc
nadine
external usenet poster
 
Posts: 303
Default 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  
Old May 8th, 2010, 12:32 AM posted to microsoft.public.excel.misc
Ed Ferrero[_3_]
external usenet poster
 
Posts: 102
Default 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  
Old May 10th, 2010, 08:22 PM posted to microsoft.public.excel.misc
nadine
external usenet poster
 
Posts: 303
Default 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  
Old May 10th, 2010, 08:24 PM posted to microsoft.public.excel.misc
nadine
external usenet poster
 
Posts: 303
Default 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  
Old May 11th, 2010, 04:07 PM posted to microsoft.public.excel.misc
Roger Govier[_8_]
external usenet poster
 
Posts: 338
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:00 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.