Lookup repeating part number in all sheets

**tomsov** · 07-06-2013, 05:50 PM

Hello, I'm really struggling to solve this problem and hope that someone can help.

I use excel to keep track of a laser cutting part list, the parts make up different components and vary from week to week - but they all are a combination of a set number of bits. The parts are ordered weekly and we need to keep track of how many of the same part is ordered, on what date it was ordered and how many we used over one year. All of the parts have a part number that is always listed in column C7 to C100 - the parts ordered may vary a lot depending on customer requirements etc.
I have a workbook with many sheets (ordered and named by date), the number of sheets grow weekly so that each week another sheet is added (named using the date).
At the moment we find that certain parts keep getting re-ordered when we have already cut the part the week before (so things get double ordered), so we need to keep a check on when the last time was that we cut the same part. We might not always cut all of the parts ordered on sheet 1 - in this case the remaining parts are copied to the new sheet 1 and the old is renamed to the date that any of the parts were cut.

What i'm trying to do is to perform a search over all of the sheets (which grow weekly), to look at the part number in column C7 to C100 on sheet 1 and compare this to all sheets in the workbook to find out when the last time this part was cut. Column F7 to F100 shows how many of the parts we cut and column G7 to G100 shows the date that the part was cut (almost always the same date as the name of the sheet).

Man i'd be really grateful if someone could help with this as i've spent loads of time going over the internet trying to find a macro or formula to perform this task.

Thanks!!

**FDibbins** · 07-06-2013, 08:14 PM

Hi and welcome to the forum

You probably dont want to hear this, but I would suggest that you put ALL data into 1 sheet, and then run extracts/searches/analysis etc on another sheet/s. Excel is VERY strong at doing summaries this way. Also, you are then pulling all your data from 1 sheet, instead of multiple sheets (much easier)

Perhaps you could upload a sample of what you are working with, and a few samples of what your expected outcome is? (no sensitive data)
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**tomsov** · 07-07-2013, 01:36 PM

Thanks for a quick reply!

I've attached the .xls that I'm working with (we've just started the database so there isn't much in there at the moment) - I've done as you suggested and compressed the 'Items Done' data into one sheet and am also working on a way to select the parts and auto copy them to the 'Items Done' sheet once the operator has completed cutting various parts.(he may not cut all of the parts on the order form in one day- as they are very time consuming to cut). From my first post, Column C has changed to D because of the new tickbox added to column A (sorry to be confusing)
My main priority is though to display the last cut date and the number of items cut (for a given part number). The part numbers will vary a lot over time up to maybe 10,000 or so different parts. There is never more than 100 parts on order at one time as we run shifts if the machine gets this busy.
We currently keep an eye on things via a paper method - and it takes a lot of time to keep check that parts do get accounted for - be it for stock or in the power coating etc.

Your help is much appreciated,

Thanks!

**tomsov** · 07-21-2013, 01:12 PM

Well i've managed to solve the problem myself with loads of playing around with various bits of code. I first of all went down creating a VB script route to try and match all the data to produce the 'last cut date' - but after nights of trawling through the excel manual i came up with a rather easy solution. Here it is incase anyone else is wanting the same result:

All items that have been cut are placed on one separate sheet, part number in the first column - this sheet grows daily with one of the columns showing the date that the part was cut.
On the first sheet (that contains the table of parts to be cut) place this code into the cell that the result will appear (the date the last time the part was cut):

=IF(ISERROR(VLOOKUP(A6,'Previous Cuts 2013'!A4:L1457,5,FALSE)),"NO RECORD",VLOOKUP(A6,'Previous Cuts 2013'!A4:L1457,5,FALSE))

This will look at cell A6 in my table, then look to my second sheet called 'previous cuts 2013' at cells A4 to L1457, column number 5 (for result - or the date) and False for the matching. I've stuck it in an IF(ISERROR( ) statement so that if it finds a blank cell then it results in "NO RECORD" until that part is added to the database.

Bobs yer uncle! Works a treat and loads simpler than I feared.

Tomsov