OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Inventory Control

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
jvmoore1
Newbie
Newbie


Joined: 10 Feb 2006
Posts: 4

PostPosted: Fri Feb 10, 2006 7:05 am    Post subject: Inventory Control Reply with quote

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! Wink
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Feb 10, 2006 8:04 am    Post subject: Reply with quote

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
View user's profile Send private message
jvmoore1
Newbie
Newbie


Joined: 10 Feb 2006
Posts: 4

PostPosted: Fri Feb 10, 2006 9:03 am    Post subject: Reply with quote

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
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Feb 10, 2006 9:46 am    Post subject: Reply with quote

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.

Smile
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
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Feb 10, 2006 10:09 am    Post subject: Reply with quote

Embarassed
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
View user's profile Send private message
jvmoore1
Newbie
Newbie


Joined: 10 Feb 2006
Posts: 4

PostPosted: Fri Feb 10, 2006 12:18 pm    Post subject: Reply with quote

Villeroy wrote:
Embarassed
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
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Feb 10, 2006 12:39 pm    Post subject: Reply with quote

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
View user's profile Send private message
jvmoore1
Newbie
Newbie


Joined: 10 Feb 2006
Posts: 4

PostPosted: Fri Feb 10, 2006 1:11 pm    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group