Prev: Check if variable is range
Next: budget setup
From: Nadine on 7 May 2010 18:03
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.

From: Ed Ferrero on 7 May 2010 19:32
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

From: Billy Liddel on 7 May 2010 21:09
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 peter_atherton(a)NOSPAMhotmail.com 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.
>
From: Nadine on 10 May 2010 15:22
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/ExcelDatabaseTutorialPart1/tabid/90/Default.aspx
>
> Ed Ferrero
> www.edferrero.com
>
> .
>
From: Nadine on 10 May 2010 15:24
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 peter_atherton(a)NOSPAMhotmail.com 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.
> >
 |  Next  |  Last
Pages: 1 2
Prev: Check if variable is range
Next: budget setup