View previous topic :: View next topic 
Author 
Message 
jvmoore1 Newbie
Joined: 10 Feb 2006 Posts: 4

Posted: Fri Feb 10, 2006 7:05 am Post subject: Inventory Control 


I have searched the forums but so far nothing seems to help..of course i am a n00b so this should be expected...
We receive 5 trucks a day with 30 rolls per truck. Each roll has its own number (i.e 31603190). so to input all i have is date roll number receiver
(example:
1. 13006 31360 m2162
2. 13006 31361 m2162)
there will be 15000 rolls so i dont really want to do the <ctrlf> to pull these rolls out.
so my thought (which can be corrected) was to make sheet 2 the outbound sheet and somehow find a rule so whenever i type the roll number in sheet 2 it will be pulled from sheet 1.
can this be done?
remember i am a n00b so treating me like a k5 child is acceptable! 

Back to top 


Villeroy Super User
Joined: 04 Oct 2004 Posts: 10106 Location: Germany

Posted: Fri Feb 10, 2006 8:04 am Post subject: 


The most simple solution requires the Rollcolumn to be the first one
Put the rollnumber in Sheet2.A1
Sheet2.A2: =VLOOKUP($A$1;$Sheet1.$A$1:$C$65536;2;0)
Looks up rollnumber in first col of A1:C65536 and gives the value of the second column.
Sheet2.A3: =VLOOKUP($A$1;$Sheet1.$A$1:$C$65536;3;0)
Looks up rollnumber in first col of A1:C65536 and gives the value of the third column.
If A1 has a rollnumber, not existing in first col of sheet1, you get #NA as result.
If (by mistake) there is more than one item with the specified number, you get the first item found. So you may use:
sheet2.B1 =COUNTIF(sheet1.$A$1:$A$65536;$A$1)
just to indicate duplicate items. 

Back to top 


jvmoore1 Newbie
Joined: 10 Feb 2006 Posts: 4

Posted: Fri Feb 10, 2006 9:03 am Post subject: 


Villeroy wrote:  The most simple solution requires the Rollcolumn to be the first one
Put the rollnumber in Sheet2.A1
Sheet2.A2: =VLOOKUP($A$1;$Sheet1.$A$1:$C$65536;2;0)
Looks up rollnumber in first col of A1:C65536 and gives the value of the second column.
Sheet2.A3: =VLOOKUP($A$1;$Sheet1.$A$1:$C$65536;3;0)
Looks up rollnumber in first col of A1:C65536 and gives the value of the third column.
If A1 has a rollnumber, not existing in first col of sheet1, you get #NA as result.
If (by mistake) there is more than one item with the specified number, you get the first item found. So you may use:
sheet2.B1 =COUNTIF(sheet1.$A$1:$A$65536;$A$1)
just to indicate duplicate items. 
ok
so i changed the layout to be roll number receiver date
A1 B1 C1
31360 m2162 13006
so on sheet 2 i posted the rule and it seems to only work for whatever roll i put in A1...
i tried dragging the rule in B2 down, but intstead of it changing the cell for teh rule it make the exact same rule (makes sense?)
also, when i type the roll number in A1 of sheet 2 the roll number in sheet 1 is still there, i need to remove this once it is in sheet 2
thank you so much for what you have done so far... 

Back to top 


Villeroy Super User
Joined: 04 Oct 2004 Posts: 10106 Location: Germany

Posted: Fri Feb 10, 2006 9:46 am Post subject: 


Quote:  so my thought (which can be corrected) was to make sheet 2 the outbound sheet and somehow find a rule so whenever i type the roll number in sheet 2 it will be pulled from sheet 1. 
We get what you requested so far.
Put the first rollnumber in Sheet2.A1
Sheet2.B1: =VLOOKUP($A1;$Sheet1.$A$1:$C$65536;2;0)
Looks up rollnumber in first col of A1:C65536 and gives the value of the second column.
Sheet2.C1: =VLOOKUP($A1;$Sheet1.$A$1:$C$65536;3;0)
Looks up rollnumber in first col of A1:C65536 and gives the value of the third column.
sheet2.D1 =COUNTIF(sheet1.$A$1:$A$65536;$A1)
I put all formulas referring to the lookupnumber into one row and the slight but important  difference: I refer to cell $A1 instead of $A$1.
Read the $ as "exactly ..."
From the view point of cell B1, $A1 is the value in the same row, but "exactly in" column A.
Copy down B1:D1 and you'll see how this rule is applied.
As a test, copy B1:D1 to the right and you'll see why I prefer keeping the $ in front of the A. It makes things easier to reorganize.
Now you may type a rollnumber in col A and get the lookupvalues and count in B to D.
A spreadsheet handles your data "as is". There is no automatic which can remove anything automaticly (some advanced builtin mechanisms are able to add data). This could be implemented with some addonprogram, often called a "macro".
It seems, you have a list of things to be done on sheet1, but I'm not shure about what kind of information you want to get on sheet2.
Anyway, I have another formula for column E:
E1: =HYPERLINK("#sheet1."&ADDRESS(MATCH($A1;sheet1.$A$1:$A$65536;0);1))
This produces a hyperlink to the cell where the value of A1 is found in col A of sheet1. Notice that quoted string "#sheet1." as first part of the hyperlink address is "frozen". Unlike the other references to sheet1 it won't be updated in case you rename sheet1. You will have to use search/replace then. 

Back to top 


Villeroy Super User
Joined: 04 Oct 2004 Posts: 10106 Location: Germany

Posted: Fri Feb 10, 2006 10:09 am Post subject: 


After rereading It's obvious that you want a list about what is done.
Formula in some column of sheet1:
=ISNUMBER(MATCH($A1;$sheet2.$A$1:$A$65536;0))
gives TRUE if there is an entry in sheet2.
Or
=IF(ISNUMBER(MATCH($A1;$sheet2.$A$1:$A$65536;0));"Done";"To Do")
Now you may select entire used columns of sheet1 and call Menu:Data>Filter>Auto Filter 

Back to top 


jvmoore1 Newbie
Joined: 10 Feb 2006 Posts: 4

Posted: Fri Feb 10, 2006 12:18 pm Post subject: 


Villeroy wrote: 
After rereading It's obvious that you want a list about what is done.
Formula in some column of sheet1:
=ISNUMBER(MATCH($A1;$sheet2.$A$1:$A$65536;0))
gives TRUE if there is an entry in sheet2.
Or
=IF(ISNUMBER(MATCH($A1;$sheet2.$A$1:$A$65536;0));"Done";"To Do")
Now you may select entire used columns of sheet1 and call Menu:Data>Filter>Auto Filter 
awesome!! everything works great excpet for the "to do" "done" part...each cell says "to do" even though i have that cell on sheet 2...
but even if that doesnt work this is great...i appreciate it soo much 

Back to top 


Villeroy Super User
Joined: 04 Oct 2004 Posts: 10106 Location: Germany

Posted: Fri Feb 10, 2006 12:39 pm Post subject: 


Mmmmh,
Do the cell of the "done"function refer to the according lookupvalue?
Hit F2 (editmode) and you get some colored borders, indicating the referred cells on the same sheet. The first formula should refer to the first rollnumber. 

Back to top 


jvmoore1 Newbie
Joined: 10 Feb 2006 Posts: 4

Posted: Fri Feb 10, 2006 1:11 pm Post subject: 


Villeroy wrote:  Mmmmh,
Do the cell of the "done"function refer to the according lookupvalue?
Hit F2 (editmode) and you get some colored borders, indicating the referred cells on the same sheet. The first formula should refer to the first rollnumber. 
yeap that was it...
thank you so much!!! i appreciate it....seriously... 

Back to top 


