Need suggestions on layout to report/pivot

[Nadine]

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.

 

[Ed Ferrero]

Hi Nadine,

Does anyone have any thoughts on how to arrange this in Excel 2003:

Some thoughts here
http://edferrero.com/ExcelTutorials/ExcelDatabaseTutorialPart1/tabid/90/Default.aspx

Ed Ferrero
www.edferrero.com

 

[Billy Liddel]

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 (e-mail address removed) and I'll post the file to you.

Do the obvious with NOSPAM

Regards
Peter

 

[Nadine]

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.

 

[Nadine]

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

 

[Roger Govier]

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