Thanks Steve,

I am trying to learn VBA, and this exercise, or something simlar, is bound
to occur again in future so I want to learn how to run the suggested loop.

I have a good understanding of Pivot tables, but they only analyse what you
put in (other than formulas etc that you can put in the tables). I am
looking to automate the population of a large amount of detail from a
summary sheet so that I have more data to work with to check the validity of
the summary data.

I have bought John Walkenbach's Power Programming book, but am struggling to
put the code into context, and this exercise will help significantly.

Regards

Craig


"STEVE BELL" wrote in message
news:x14re.4178$9a1.692@trnddc01...
You may want to check out Pivot Tables.
This can be done in Excel without code.
Or you can creat them in code.

You can add calculation columns to the table(s)

--
steveB

Remove "AYN" from email to respond
"Craig" wrote in message
...
Sorry, the formatting seems to change when I send it. Will try in RTF and
hopefully it stays the same.

Summary date :

Job No Job value Sales Value of work to date %
Complete Division WIP
10256 50,000 15,000
40% 1 20,000
These
are not broken down by Department
10257 100,000 15,000
10% 2 10,000


The detailed info would be as follows (circa 20,000 lines per month):

Job No Description Division Department
Quantity
Unit Sale Price Total Sale Price Unit Standard Cost Total
Std Cost
10256 Machine 1 1 1
5 500 2500
260 1300
10256 Machine 2 1 2
3 200 600
160 480
10256 Machine 1 1 1
1 500 500
260 260
10256 Machine 3 1 3
4 300 1200
200 800
10256 etc

What I think I need to do is the following:

1. Name the range in which the summary info is held.
2. Get the first job no. in the summary info
Filter the detailed information by this job number and then subtotal the
Total Sales Price column
Lookup the Value of work to date on the summary sheet and apply the value
to a cell.
Divide the Detailed Total Sales Price by the lookup value.
Apply the % achieved by this formula to all the cells in the Filtered
Detail
page (Giving me a departmental WIP as the % would be applied to each
detailed line)
3. Loop to the next job in the summary information


Craig


"JMMach" wrote in message
...
It would appear your first task is to rearrange how your data is
recorded;
you need a list or a table of your Data. That is, list the categories
of
information as headings in ONE ROW - Field Names - and keep all related
information about a particular job in ONE ROW - a Record. JobNo should
be
the first Field Name if you plan on using VLOOKUPs and the like. Only
then
will your stated task become reasonably doable.
I hope that helps.
TTFN
JMMach

"Craig" wrote in message
...
I am new to macros and can get by with basic programming, but struggle
with
loops etc.

I currently receive summary date of jobs (circa 100 per month) and
their
estimated Work in Progress values on a monthly basis. The estimated %
completion on each job is an estimate based on the relevant
individuals
"Gut
Feel" based on how the job is progressing, rather than a more
"mathematical"
approach. I have access to the detail which makes up the summary
information
and would like to apply a macro to test the "reasonability" of the
summary
figures I am receiving.

The summary info is in the following format:

Job No Job value Sales Value of work to date
%
Complete Division WIP
10256 50,000 15,000
40% 1 20,000
These are not broken down by Department
10257 100,000 15,000
10% 2 10,000

The Value of Work to date is Sales price x Time spent and would not
take
into account efficiencies / inefficinecies in production, so job 10256
is
40% complete despite the value of work to date only amounting to 30%
of
the
job value.


The detailed info would be as follows (circa 20,000 lines per month):

Job No Description Division Department
Quantity Unit Sale Price Total Sale Price
Unit
Standard Cost Total Std Cost
10256 Machine 1 1 1
5 500 2500
260 1300
10256 Machine 2 1 2
3 200 600
160 480
10256 Machine 1 1 1
1 500 500
260 260
10256 Machine 3 1 3
4 300 1200
200 800
10256 etc


What I think I need to do is the following:

1. Name the range in which the summary info is held.
2. Get the first job no. in the summary info
Filter the detailed information by this job number and
then
subtotal the Total Sales Price column
Lookup the Value of work to date on the summary sheet and
apply
the value to a cell.
Divide the Detailed Total Sales Price by the lookup value.
Apply the % achieved by this formula to all the cells in
the
in
the Filtered Detail page (This would then give me a departmental WIP
as
the
% would be applied to each
detailed
line)
Loop to the next job in the summary information

I am currently doing this manually and it is extremely slow.

Any help would be appreciated. Please let me know if you need any of
this
needs further clarification. I am interested in the macro to achieve
the
above. The logic of what is being done is difficult to explain as
there
are
further levels of detail which would need analysis, but sorting the
macro
out would clear a lot of them up, as well as providing me with the
model
for further analysis.

Thanks in advance.

Craig