equipment service tracking

[Ongoing Equipment Service Detail-Date/BR]

Hello, We have hundreds of heavy machinery equipment located thru out
different branches. Once a month I get a list of equipment (hundreds) that I
need to go thru and select which ones are copleted, outstanding and In
Process and how longs its been (90 Days, 180 Days, Under 270 Days & Over 270
Days) and how many hours it took.

Once gone thru this list of equipment and making it completed or outstanding
or in process, I put an "X" in the column that it pertains to, whether it was
in 90 days etc. and how many hours it was completed in.

After all of that , I have to get a summary of each branch and how many
completed/oustanding/inprocess and how many hours it took.

What i did was create a sheet for each branch and put their data
accordingly, then I created a summary sheet with SumIf formulas in there.

Is there an easier way for me to do this all off of the original sheet I get
with all the equipemtn on there, instead of having to go thru it and putting
them in there according branch sheet. I was hoping for a macro that would
calculate it for me.

Help would be very much appreciated.

 

[SysAccountant]

You have a couple of options:

1. Use Pivot Table
2.Use MS Query Tool (DATA --> IMPORT EXTERNAL DATA --> IMPORT DATA) - then
choose the "Excel" driver (N.B. you may have to name the range that you want
to analyse).

Both of the options don't need to be done programmatically - but if you wish
you can do so.


From what you have described PivotTables (PT) seem your best option -
because PT have a functionality for creating ("exploding") several sheets
from one sheet.Therefore what you can do is analyse the data by branch and
then use the facility to create the required number of branches (as Excel
goes through the whole data list and does the work for you.).

If you need further elaboration , let me know.

Regards

SysAccountant