| 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 3160-3190). so to input all i have is date roll number receiver
(example:
1. 1-30-06 31360 m-2162
2. 1-30-06 31361 m-2162)
there will be 15000 rolls so i dont really want to do the <ctrl-f> 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 k-5 child is acceptable!  |
|
| Back to top |
|
 |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Fri Feb 10, 2006 8:04 am Post subject: |
|
|
The most simple solution requires the Roll-column to be the first one
Put the roll-number in Sheet2.A1
Sheet2.A2: =VLOOKUP($A$1;$Sheet1.$A$1:$C$65536;2;0)
Looks up roll-number 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 roll-number in first col of A1:C65536 and gives the value of the third column.
If A1 has a roll-number, 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 Roll-column to be the first one
Put the roll-number in Sheet2.A1
Sheet2.A2: =VLOOKUP($A$1;$Sheet1.$A$1:$C$65536;2;0)
Looks up roll-number 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 roll-number in first col of A1:C65536 and gives the value of the third column.
If A1 has a roll-number, 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 m-2162 1-30-06
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: 10065
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 roll-number in Sheet2.A1
Sheet2.B1: =VLOOKUP($A1;$Sheet1.$A$1:$C$65536;2;0)
Looks up roll-number 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 roll-number 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 lookup-number 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 roll-number in col A and get the lookup-values 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 addon-program, 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: 10065
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: 10065
Location: Germany
|
| Posted: Fri Feb 10, 2006 12:39 pm Post subject: |
|
|
Mmmmh,
Do the cell of the "done"-function refer to the according lookup-value?
Hit F2 (edit-mode) and you get some colored borders, indicating the referred cells on the same sheet. The first formula should refer to the first roll-number. |
|
| 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 lookup-value?
Hit F2 (edit-mode) and you get some colored borders, indicating the referred cells on the same sheet. The first formula should refer to the first roll-number. |
yeap that was it...
thank you so much!!! i appreciate it....seriously... |
|
| Back to top |
|
|
|
