I maintain a status sheet of hundreds of assets (listed in the rows). The
columns contain various fields that capture what state each asset is in.
This gives me a row by column status table. The status is updated daily, so
changes to fields are made by overwriting the old field value with the new.
Is it possible to track the changes to these fields over time? Currently, I
make updates to the table and save it as a different name to preserve the old
state. Frequently, I have to go open an old status sheet to see what state
an asset was in at a previous time. I would like to have all data, old and
current on one sheet.

Is there an easy way to acheive this? I envision a 3 dimensional matrix in
which the 3rd dimension represents time - this would give me a asset by field
by time status sheet.

I use Excel 2003 ver 11.x

Pivot table?

- David

aperez11 wrote:
I maintain a status sheet of hundreds of assets (listed in the rows). The
columns contain various fields that capture what state each asset is in.
This gives me a row by column status table. The status is updated daily, so
changes to fields are made by overwriting the old field value with the new.
Is it possible to track the changes to these fields over time? Currently, I
make updates to the table and save it as a different name to preserve the old
state. Frequently, I have to go open an old status sheet to see what state
an asset was in at a previous time. I would like to have all data, old and
current on one sheet.

Is there an easy way to acheive this? I envision a 3 dimensional matrix in
which the 3rd dimension represents time - this would give me a asset by field
by time status sheet.

I use Excel 2003 ver 11.x

You're looking at a database - either in Access, or if you want to
keep it a spreadsheet with multiple worksheets.

In a database form, you have one table representing assets, and
another tracking each status change by date. So, the columns in your
asset table are Asset ID, Asset Name, Asset Type, Asset Location,
Serial Number, In-Service Date, and whatever other information you
need to track. Your second table, the status table, will have these
fields: Asset ID, Status, Status Date, and any information pertinent
to the status changing. The assumption here is that Asset ID uniquely
identifies each of your assets.

Now, in a database, getting the most recent status for each asset is
easy, with a query like this: "SELECT tblAssets.assetName,
MAX(tblStatus.statusDate) AS CurrentStatus FROM tblAssets INNER JOIN
tblStatus on tblAssets.assetId = tblStatus.assetId GROUP BY
tblAssets.assetName". You can also look up status history for each
asset with a similar query. Access will allow you to build these
using QBE without having to mess with SQL, and if you want to bring
these into Excel for whatever reason, you can always use MSQuery.

In Excel, you can use lookup fields and filters to achieve similar
results. In my opinion, Excel is not terribly well suited for this
sort of application, so I would recommend migrating to an Access
database. If you do prefer to stick with Excel, you might find this
site useful:

http://www.edferrero.com/ExcelTutori...0/Default.aspx




On Sep 4, 4:32 pm, aperez11
wrote:
I maintain a status sheet of hundreds of assets (listed in the rows). The
columns contain various fields that capture what state each asset is in.
This gives me a row by column status table. The status is updated daily, so
changes to fields are made by overwriting the old field value with the new.
Is it possible to track the changes to these fields over time? Currently, I
make updates to the table and save it as a different name to preserve the old
state. Frequently, I have to go open an old status sheet to see what state
an asset was in at a previous time. I would like to have all data, old and
current on one sheet.

Is there an easy way to acheive this? I envision a 3 dimensional matrix in
which the 3rd dimension represents time - this would give me a asset by field
by time status sheet.

I use Excel 2003 ver 11.x