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.

Need suggestions on layout to report/pivot
 

Hi Nadine

Do not use extra columns, as this will make your data very difficult to
Pivot.
instead use Rows

With the following layout
Inv No Amount Date Hrs
1001 1000 01/01/2010 100
1002 500 15/01/2010 50
1001 -200 01/02/2010 -20
1001 -500 01/03/2010 -50

Just enter your payments in exactly the same way as your Invoices, just
make the Amount and the Hours negative.

I would make the information in the form of a Table. Insert tabTablemy
table has Headers
Then insert a new row above the table and in B1 enter
=SUBTOTAL(109,Table1[Amount])
and in D1
=SUBTOTAL(109,Table1[Hrs])

Using the dropdown on Inv No, selecting any Invoice will filter the
table to show all of the Debit and Credit amounts against Invoice, and
in B1 the Balance of Amount will show, and in D1 the Balance of Hours.

The data will be in an ideal format to Pivot.
--
Regards
Roger Govier

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.

Need suggestions on layout to report/pivot
 

Hi Roger,

If I'm reading your comment correctly, my number of rows would grow
tremendously as I already have over 1000 rows for one month's invoice data.
Also, this will require the customer to search for all rows pertaining to a
particular invoice in order to find all the payment information. By entering
negative hours when payments are made will no longer give the total hours per
resource. This is information that is important to have. I don't see an
option to add a Table in Excel and don't want subtotals but maybe I'm
misunderstanding this one since I don't know how to insert a table in the
first place. Thanks so much for your thought on this but unfortunately I
don't think this will work. Thanks for taking the time to give some thought
to my problem.


"Roger Govier" wrote:

Hi Nadine

Do not use extra columns, as this will make your data very difficult to
Pivot.
instead use Rows

With the following layout
Inv No Amount Date Hrs
1001 1000 01/01/2010 100
1002 500 15/01/2010 50
1001 -200 01/02/2010 -20
1001 -500 01/03/2010 -50

Just enter your payments in exactly the same way as your Invoices, just
make the Amount and the Hours negative.

I would make the information in the form of a Table. Insert tabTablemy
table has Headers
Then insert a new row above the table and in B1 enter
=SUBTOTAL(109,Table1[Amount])
and in D1
=SUBTOTAL(109,Table1[Hrs])

Using the dropdown on Inv No, selecting any Invoice will filter the
table to show all of the Debit and Credit amounts against Invoice, and
in B1 the Balance of Amount will show, and in D1 the Balance of Hours.

The data will be in an ideal format to Pivot.
--
Regards
Roger Govier

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.
.

Need suggestions on layout to report/pivot
 

Hi Nadine

If you would like to mail me direct with a copy of your workbook, I will
set up what I mean.
Filtering will prevent a user having to search for anything.
Don't enter negative hours if you don't want to.
12000 rows is now a big spreadsheet by today's standards.

If you would like to mail me direct with a copy of your workbook, I will
set up what I mean.
To mail direct
roger at technology4u dot co dot uk
Change the at and dots and remove spaces to make valid email address.
--
Regards
Roger Govier

Nadine wrote:
Hi Roger,

If I'm reading your comment correctly, my number of rows would grow
tremendously as I already have over 1000 rows for one month's invoice data.
Also, this will require the customer to search for all rows pertaining to a
particular invoice in order to find all the payment information. By entering
negative hours when payments are made will no longer give the total hours per
resource. This is information that is important to have. I don't see an
option to add a Table in Excel and don't want subtotals but maybe I'm
misunderstanding this one since I don't know how to insert a table in the
first place. Thanks so much for your thought on this but unfortunately I
don't think this will work. Thanks for taking the time to give some thought
to my problem.


"Roger Govier" wrote:

Hi Nadine

Do not use extra columns, as this will make your data very difficult to
Pivot.
instead use Rows

With the following layout
Inv No Amount Date Hrs
1001 1000 01/01/2010 100
1002 500 15/01/2010 50
1001 -200 01/02/2010 -20
1001 -500 01/03/2010 -50

Just enter your payments in exactly the same way as your Invoices, just
make the Amount and the Hours negative.

I would make the information in the form of a Table. Insert tabTablemy
table has Headers
Then insert a new row above the table and in B1 enter
=SUBTOTAL(109,Table1[Amount])
and in D1
=SUBTOTAL(109,Table1[Hrs])

Using the dropdown on Inv No, selecting any Invoice will filter the
table to show all of the Debit and Credit amounts against Invoice, and
in B1 the Balance of Amount will show, and in D1 the Balance of Hours.

The data will be in an ideal format to Pivot.
--
Regards
Roger Govier

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.
.