I have a excel file with 8000 rows. It is an worksheet of AR invoice aging.
I want to be able to create spreadsheets for the following. 1. Total Company
Aging - (summary of this spredsheet) 2. Summary Aging per Customer #
(summary of each customer) I have a total of 400 customers in this file. I
don't know how to use VGA - just formuals. I can go the vlook ups, but I am
just a beginner with those. Any suggestions/ideas as to how I can manage
this data? If you have other ideas I would like to hear them. I know
someone out there has done this before. At my old job I had some programers
that could do this for me. Here I am that person if I want the report.
HELP!! Thanks!
EX: these are the column headings that I have. I have to manually age
into the correct column because the system can't do it.
Customer Name AR # Custome Service# Invoice Date Invoice Total
Invoice# PO# Invoice Terms Due Date Current 1-30 31-60 61-90 91-120 120

davies wrote:
I have a excel file with 8000 rows. It is an worksheet of AR invoice
aging.
I want to be able to create spreadsheets for the following. 1. Total
Company Aging - (summary of this spredsheet) 2. Summary Aging per
Customer # (summary of each customer) I have a total of 400 customers
in this file. I don't know how to use VGA - just formuals. I can go
the vlook ups, but I am just a beginner with those. Any
suggestions/ideas as to how I can manage this data? If you have
other ideas I would like to hear them. I know someone out there has
done this before. At my old job I had some programers that could do
this for me. Here I am that person if I want the report. HELP!!
Thanks!
EX: these are the column headings that I have. I have to manually
age into the correct column because the system can't do it.
Customer Name AR # Custome Service# Invoice Date Invoice Total
Invoice# PO# Invoice Terms Due Date Current 1-30 31-60 61-90 91-120
120

Hi Davies,

maybe you could upload an example file to www.savefile.com


--
Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Hi Franz what is savefile.com? I am not familiar with that. - Davies

"Franz Verga" wrote:

davies wrote:
I have a excel file with 8000 rows. It is an worksheet of AR invoice
aging.
I want to be able to create spreadsheets for the following. 1. Total
Company Aging - (summary of this spredsheet) 2. Summary Aging per
Customer # (summary of each customer) I have a total of 400 customers
in this file. I don't know how to use VGA - just formuals. I can go
the vlook ups, but I am just a beginner with those. Any
suggestions/ideas as to how I can manage this data? If you have
other ideas I would like to hear them. I know someone out there has
done this before. At my old job I had some programers that could do
this for me. Here I am that person if I want the report. HELP!!
Thanks!
EX: these are the column headings that I have. I have to manually
age into the correct column because the system can't do it.
Customer Name AR # Custome Service# Invoice Date Invoice Total
Invoice# PO# Invoice Terms Due Date Current 1-30 31-60 61-90 91-120
120

Hi Davies,

maybe you could upload an example file to www.savefile.com


--
Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Hi
I assume from your layout that Invoice Date is in Column D and Current
is in Column J.
I would be inclined to do away with the columns for the aging, and just
use a single formula in column J, which I would give the heading Period.
In J2
=MIN(4,INT((TODAY()-D2)/30))
copy down column as far as required
This would calculate a period number, 0 for current, 1 for 1 to 30
days, 2 for 31 to 60 etc.

Then I would use a Pivot Table for my summary.
Mark the block of dataDataPivot TablesNextNextLayout
Drag Customer Name to the Row area
Drag Period to the Column area
Drag Invoice Total to the Data Area
Click OKNext to put summary on a new sheet

You will then have your data summarised by period and Customer, with a
Total for the company for each period and overall.

For more help on Pivot Tables, including using a Dynamic Range to define
the data area, take a look at Debra Dalgleish's site and scroll to the
section on Pivot Tables.
http://www.contextures.com/tiptech.html


--
Regards

Roger Govier


"davies" wrote in message
...
I have a excel file with 8000 rows. It is an worksheet of AR invoice
aging.
I want to be able to create spreadsheets for the following. 1. Total
Company
Aging - (summary of this spredsheet) 2. Summary Aging per Customer #
(summary of each customer) I have a total of 400 customers in this
file. I
don't know how to use VGA - just formuals. I can go the vlook ups,
but I am
just a beginner with those. Any suggestions/ideas as to how I can
manage
this data? If you have other ideas I would like to hear them. I know
someone out there has done this before. At my old job I had some
programers
that could do this for me. Here I am that person if I want the
report.
HELP!! Thanks!
EX: these are the column headings that I have. I have to manually
age
into the correct column because the system can't do it.
Customer Name AR # Custome Service# Invoice Date Invoice Total
Invoice# PO# Invoice Terms Due Date Current 1-30 31-60 61-90 91-120
120

Hi Roger,
You assumed correctly. I am trying your suggestion however my Column J is
returning all '4'. I am not familiar with that formual. I understand the
functionality of it but I can't figure out why I am getting 4. Here is an
example:

(D) (J)
Invoice Date Current
06/09/2006 4
Formula in J = =MIN(4,INT((TODAY()-D2/30)))

It should return a 1 correct?
Thank you for your suggestions!

Hi

You didn't copy my formula, you typed it and got a bracket in he wrong
place

= MIN (4, INT( (TODAY()-D2) /30) ) spaces inserted for clarity
not
=MIN(4, INT( (TODAY()-D2/30)) )

The former will return 1, where D2 contains 09 Jun 2006

--
Regards

Roger Govier


"davies" wrote in message
...
Hi Roger,
You assumed correctly. I am trying your suggestion however my Column
J is
returning all '4'. I am not familiar with that formual. I understand
the
functionality of it but I can't figure out why I am getting 4. Here
is an
example:

(D) (J)
Invoice Date Current
06/09/2006 4
Formula in J = =MIN(4,INT((TODAY()-D2/30)))

It should return a 1 correct?
Thank you for your suggestions!

davies wrote:
Hi Franz what is savefile.com? I am not familiar with that. - Davies

it's a site for uploading files and sharing them. In this way you don't need
to attach the file, just post or e-mail the link...


"Franz Verga" wrote:

davies wrote:
I have a excel file with 8000 rows. It is an worksheet of AR invoice
aging.
I want to be able to create spreadsheets for the following. 1.
Total Company Aging - (summary of this spredsheet) 2. Summary
Aging per Customer # (summary of each customer) I have a total of
400 customers in this file. I don't know how to use VGA - just
formuals. I can go the vlook ups, but I am just a beginner with
those. Any suggestions/ideas as to how I can manage this data? If
you have other ideas I would like to hear them. I know someone out
there has done this before. At my old job I had some programers
that could do this for me. Here I am that person if I want the
report. HELP!! Thanks!
EX: these are the column headings that I have. I have to manually
age into the correct column because the system can't do it.
Customer Name AR # Custome Service# Invoice Date Invoice Total
Invoice# PO# Invoice Terms Due Date Current 1-30 31-60 61-90 91-120
120

Hi Davies,

maybe you could upload an example file to www.savefile.com


--
Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Have a look at this example, adjust to suit

http://www.savefile.com/files/4350447


"davies" wrote in message
...
I have a excel file with 8000 rows. It is an worksheet of AR invoice
aging.
I want to be able to create spreadsheets for the following. 1. Total
Company
Aging - (summary of this spredsheet) 2. Summary Aging per Customer #
(summary of each customer) I have a total of 400 customers in this file.
I
don't know how to use VGA - just formuals. I can go the vlook ups, but I
am
just a beginner with those. Any suggestions/ideas as to how I can manage
this data? If you have other ideas I would like to hear them. I know
someone out there has done this before. At my old job I had some
programers
that could do this for me. Here I am that person if I want the report.
HELP!! Thanks!
EX: these are the column headings that I have. I have to manually age
into the correct column because the system can't do it.
Customer Name AR # Custome Service# Invoice Date Invoice Total
Invoice# PO# Invoice Terms Due Date Current 1-30 31-60 61-90 91-120 120