Create a history movement of equipment from separate table or within the table

[dpvilar]

Hello Guys am new here and am very happy to be part of this forum.....

I want to create something which I hope is possible:

I have a table with list of equipments and asset number assigned as primary key, I wanted to have a history movement of these equipments but my idea is somehow not logical and very primitive to update every time 2 excel sheets, maybe you guys have some brilliant idea to help me out:

ie:

[TABLE="width: 500"]
<tbody>[TR]
[TD]asset no[/TD]
[TD]description[/TD]
[TD]received by[/TD]
[TD]day[/TD]
[TD]month[/TD]
[TD] year[/TD]
[TD]location[/TD]
[/TR]
[TR]
[TD]623123[/TD]
[TD]truck[/TD]
[TD]foreman1[/TD]
[TD]1[/TD]
[TD]december[/TD]
[TD]2012[/TD]
[TD]asset area[/TD]
[/TR]
</tbody>[/TABLE]

history of movement as follows:

[TABLE="width: 500"]
<tbody>[TR]
[TD]asset no[/TD]
[TD]received by[/TD]
[TD]from location[/TD]
[TD]day[/TD]
[TD]month[/TD]
[TD]year[/TD]
[TD]to location[/TD]
[TD]day[/TD]
[TD]month[/TD]
[TD]year[/TD]
[/TR]
[TR]
[TD]623123[/TD]
[TD]foreman2 [/TD]
[TD]asset[/TD]
[TD]1[/TD]
[TD]dec[/TD]
[TD]2012[/TD]
[TD]fabrication[/TD]
[TD]3[/TD]
[TD]dec[/TD]
[TD]2012[/TD]
[/TR]
[TR]
[TD]623123[/TD]
[TD]foreman3[/TD]
[TD]fabrication[/TD]
[TD]6[/TD]
[TD]dec[/TD]
[TD]2012[/TD]
[TD]welding[/TD]
[TD]7[/TD]
[TD]dec[/TD]
[TD]2012[/TD]
[/TR]
[TR]
[TD]623123[/TD]
[TD]foreman1[/TD]
[TD]welding[/TD]
[TD]8[/TD]
[TD]dec[/TD]
[TD]2012[/TD]
[TD]asset[/TD]
[TD]9[/TD]
[TD]dec[/TD]
[TD]2012[/TD]
[/TR]
</tbody>[/TABLE]

thanks guys...MORE POWER.

 

[Akashwani]

Hi and welcome to MrExcel.

Are you saying that you want to look up a particular Asset No and show all movements for it?
If so, then try this....

Excel Workbook
	A	B	C	D	E	F	G
1	Asset No	Description	Received by	Day	Month	Year	Location
2	623123	truck	foreman1	1	december	2012	asset area
3	623124	truck	foreman2	2	december	2012	asset area
4	623123	Car	foreman3	3	december	2012	asset area
5	623124	truck	foreman4	4	december	2012	asset area
6	623123	truck	foreman5	5	december	2012	asset area
7	623124	truck	foreman6	6	december	2012	asset area
8	623123	Big Truck	foreman7	7	december	2012	asset area
9	623124	truck	foreman8	8	december	2012	asset area
Equipment

History Results....

Excel Workbook
	A	B	C	D	E	F	G	H
1	4
2
3	Asset No	Description	Received by	Day	Month	Year	Location
4	623123	truck	foreman1	1	december	2012	asset area
5		Car	foreman3	3	december	2012	asset area
6		truck	foreman5	5	december	2012	asset area
7		Big Truck	foreman7	7	december	2012	asset area
8
EquipHistory

The sheet and cell references will need to be changed to suit yours.
The formula in B4 needs entering with ctrl shift enter NOT just enter, you can the copy it across and down.

I hope that helps.

Good luck.

Ak

 

[dpvilar]

nice idea bro....yes I think that can help and can make a summarized movement thank you! I will try this now...