Need an Excel Wizard's brain!!!!!!

[Desu Nota from Columbus]

I need a way to manage part of our inventory. The product I am interested in is an embossing roll.

The product moves from place to place within the factory, and then occasionally gets sent out to a vendor to be fixed. We never buy/sell rolls, we just need a means of tracking where and when a roll was moved and the condition it is in.

We have each embossing roll engraved with a unique serial number--and each serial number has certain physical attributes attached that do not change.

What I need to accomplish:

1) Create a way to keep track of the location, date of arrival to location, and condition of the roll.

2) Track the rolls based on their serial numbers (and have the physical characteristics like pattern, size, and coating attached at all time)

However this is to be accomplished, it must very easy and hopefully done through excel. Only the location, date, and condition will need to be updated daily; the other data only changes under extreme circumstances.



**** And we have around 60 embossing rolls****


Any guidance or suggestions would be well received. I would like to stay away from access if possible (I dislike it and don't understand it thoroughly)

 

[Akashwani]

Hi,

I'm not a Wizard, so don't expect too much or anything special.

How many Locations do you have?
Can you use something like this?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Serial Number	Pattern	Size	Coating	Location A	Condition	Location B	Condition	Location C	Condition
2	Roll1234	XX	1200 x 1000	AAA	12/04/2011	Stage1	*	*	*	*
3	Roll1235	Pretty	1200 x 900	BBB	11/04/2011	Stage1	*	*	*	*
4	Roll1236	Dotted	1200 x 1000	CCC	02/04/2011	Stage1	09/04/2011	Stage2	*	*
5	Roll1237	XYZ	1200 x 900	AAA	22/03/2011	Stage1	29/03/2011	Stage2	01/04/2011	Finished
6	Roll1238	Tartan	1200 x 1000	AAA	10/03/2011	Stage1	27/03/2011	Stage2	02/04/2011	Finished
7	Roll1239	Stripe	1200 x 900	BBB	12/04/2011	Stage1	*	*	*	*
8	Roll1240	Circle	1200 x 1000	CCC	11/04/2011	Stage1	*	*	*	*
9	Roll1241	Raised	1200 x 900	AAA	12/03/2011	Stage1	26/03/2011	Stage2	05/04/2011	Finished
10	Roll1242	Twisted	1200 x 1000	AAA	18/04/2011	Stage1	*	*	*	*
Sheet1

And this.....

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	*	*	*	*	*	*	*	*	*	*	*	*	*
2	*	Enter a Serial Number	Pattern	Size	Coating	Location A	Condition	Location B	Condition	Location C	Condition	*	*
3	*	Roll1234	XX	1200 x 1000	AAA	12/04/2011	Stage1	0	0	0	0	*	*
4	*	*	*	*	*	*	*	*	*	*	*	*	*
5	*	*	*	*	*	*	*	*	*	*	*	*	*
6	*	Select a Location	*	*	*	*	*	*	*	*	*	*	*
7	3	Finished	Serial Number	Pattern	Size	Coating	Location A	Condition	Location B	Condition	Location C	Condition	*
8	*	*	Roll1237	XYZ	1200 x 900	AAA	22/03/2011	Stage1	29/03/2011	Stage2	01/04/2011	Finished	*
9	*	*	Roll1238	Tartan	1200 x 1000	AAA	10/03/2011	Stage1	27/03/2011	Stage2	02/04/2011	Finished	*
10	*	*	Roll1241	Raised	1200 x 900	AAA	12/03/2011	Stage1	26/03/2011	Stage2	05/04/2011	Finished	*
11	*	*	*	*	*	*	*	*	*	*	*	*	*
12	*	*	*	*	*	*	*	*	*	*	*	*	*
13	*	*	*	*	*	*	*	*	*	*	*	*	*
14	*	*	*	*	*	*	*	*	*	*	*	*	*
Sheet2

Sheet1 could be your daily input sheet, sheet2 can be used to look up the progress of an individual Serial number or a location/stage.

I hope that helps point you in the right direction.

Good luck

 

[Desu Nota from Columbus]

Thanks for the response.

How do you setup sheet 2?

I have never been able to figure out the search for string function.

 

[Akashwani]

Hi,

Visit ExcelIsFun at youtube for some great videos on this, Mike explains it all really well...

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	*	*	*	*	*	*	*	*	*	*	*	*	*
2	*	Enter a Serial Number	Pattern	Size	Coating	Location A	Condition	Location B	Condition	Location C	Condition	*	*
3	*	Roll1234	XX	1200 x 1000	AAA	12/04/2011	Stage1	0	0	0	0	*	*
4	*	*	*	*	*	*	*	*	*	*	*	*	*
5	*	*	*	*	*	*	*	*	*	*	*	*	*
6	*	Select a Location	*	*	*	*	*	*	*	*	*	*	*
7	3	Finished	Serial Number	Pattern	Size	Coating	Location A	Condition	Location B	Condition	Location C	Condition	*
8	*	*	Roll1237	XYZ	1200 x 900	AAA	22/03/2011	Stage1	29/03/2011	Stage2	01/04/2011	Finished	*
Sheet2

The formula used in row 8 can be copied across and down as far as you like.
You will need an expert to explain it to you though, I just know how to modify it to meet my needs

The data on Sheet1 can also be used to track the progress of the roll by using Conditional Formatting and an IF Formula.....

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Serial Number	Pattern	Size	Coating	Location A	Condition	Location B	Condition	Location C	Condition	No of days	20 Days Max	18/04/2011	*
2	Roll1234	XX	1200 x 1000	AAA	12/04/2011	Stage1	*	*	*	*	6	Stage1	*	Stage1
3	Roll1235	Pretty	1200 x 900	BBB	11/04/2011	Stage1	*	*	*	*	7	Stage1	*	Stage2
4	Roll1236	Dotted	1200 x 1000	CCC	02/04/2011	Stage1	09/04/2011	Stage2	*	*	16	Stage2	*	On Target
5	Roll1237	XYZ	1200 x 900	AAA	22/03/2011	Stage1	29/03/2011	Stage2	01/04/2011	Finished	10	On Target	*	Over Schedule
6	Roll1238	Tartan	1200 x 1000	AAA	10/03/2011	Stage1	27/03/2011	Stage2	02/04/2011	Finished	23	Over Schedule	*	*
7	Roll1239	Stripe	1200 x 900	BBB	12/04/2011	Stage1	15/04/2011	Stage2	*	*	6	Stage2	*	*
8	Roll1240	Circle	1200 x 1000	CCC	11/04/2011	Stage1	*	*	*	*	7	Stage1	*	*
9	Roll1241	Raised	1200 x 900	AAA	12/03/2011	Stage1	26/03/2011	Stage2	05/04/2011	Finished	24	Over Schedule	*	*
10	Roll1242	Twisted	1200 x 1000	AAA	18/04/2011	Stage1	*	*	*	*	0	Stage1	*	*
Sheet1

I know the colours are a bit loud, but at a glance you can see the progress. I have done a count in column K of how long the process has taken, then in column L I have put a formula to inform the user what stage it is at and if you have a maximum time period to get from start to finish it will inform you if it has taken too long. All that is irrelevant though I guess.

Good luck

 

[Desu Nota from Columbus]

This is a premature thank you, as this will probably be more useful than I can imagine at the moment. Thank you!

I will sort through all this now.