Notices
Results 1 to 17 of 17

Thread: Excel question

  1. #1 Excel question 
    Forum Bachelors Degree Shaderwolf's Avatar
    Join Date
    Dec 2005
    Location
    OPSEC, baby. Sorry.
    Posts
    425
    Hello, I'm trying to create an excel file for work that will make the job easier for everyone here but I'm having some troubles.

    We are a storage facility, and every day people shops we work with come and check materials in from us and have to give them back. Origionally recording who has what was done by paper. This should be better.

    There are two main identifiers I'm using to classify each item. One, I call the "Object Code" or OC for short. The other is the serial number each came with. Unit Index Code tells us "what it is" and the serial lets us keep track of each individual item.

    There are a number of shops we work with. When they come in, I take an item off of our large inventory list and insert it onto a worksheet with their shop name. Vice versa when they come back.

    First thing, I want to make a referance page, that tells me who has what at a quick glance. For this all I need is the Object Code.

    I created a dropdown list using data validation containing the name of each tab. I was hoping to use this as a variable in a COUNTIF formula in a column of cells that counted the times the OC which is located in the cell on the left hand of each individual cell occured in the tab the data validation cell refers to. I want to have the reference column only show OCs that occur in the tab wich the data validation cell refers to. I cannot insert pictures for various reasons so I hope you can understand what I'm asking.


    Here's the problem with questions like "what would we see if we traveled faster than the speed of light". Since the rules that govern the universe as we understand them do not allow for such a possibility, to imagine such an event forces us to abandon those rules. But that leaves us no guide by which to answer the question. We have no idea as to what rules to replace them with, and we can't give an answer. - Janus
    Reply With Quote  
     

  2.  
     

  3. #2  
    Forum Bachelors Degree Shaderwolf's Avatar
    Join Date
    Dec 2005
    Location
    OPSEC, baby. Sorry.
    Posts
    425
    ...do I need to clarify at all?


    Here's the problem with questions like "what would we see if we traveled faster than the speed of light". Since the rules that govern the universe as we understand them do not allow for such a possibility, to imagine such an event forces us to abandon those rules. But that leaves us no guide by which to answer the question. We have no idea as to what rules to replace them with, and we can't give an answer. - Janus
    Reply With Quote  
     

  4. #3  
    ▼▼ dn ʎɐʍ sıɥʇ ▼▼ RedPanda's Avatar
    Join Date
    Aug 2012
    Location
    UK
    Posts
    2,737
    Hi
    Quote Originally Posted by Shaderwolf View Post
    First thing, I want to make a referance page, that tells me who has what at a quick glance. For this all I need is the Object Code.
    Are you looking to make a single worksheet that contains all the records from the different "worksheet[s] with their shop name"?

    Quote Originally Posted by Shaderwolf View Post
    I created a dropdown list using data validation containing the name of each tab. I was hoping to use this as a variable in a COUNTIF formula in a column of cells that counted the times the OC which is located in the cell on the left hand of each individual cell occured in the tab the data validation cell refers to. I want to have the reference column only show OCs that occur in the tab wich the data validation cell refers to. I cannot insert pictures for various reasons so I hope you can understand what I'm asking.
    Would it not be better to have just the "large inventory list" and simply add a column called (e.g.) "Given To Shop" where you put the name of the shop that has the item?
    You could then easily use filters to see what each shop has, or which shops have a specific OC, etc.

    So, my suggestion is to basically maintain a single "large inventory list" but add 'flag/s' to each record to show its 'status'.
    SayBigWords.com/say/3FC

    "And, behold, I come quickly;" Revelation 22:12

    "Religions are like sausages. When you know how they are made, you no longer want them."
    Reply With Quote  
     

  5. #4  
    Forum Bachelors Degree Shaderwolf's Avatar
    Join Date
    Dec 2005
    Location
    OPSEC, baby. Sorry.
    Posts
    425
    I was hoping for it to be a dynamic worksheet that you can eventually use to pull up specific pieces of information, including who has what, how much of what is where, etc, and eventually (this is the harder part for me) one that you could use to change these facts, such as adding or removing an item (each item actually has 8 columns of information including the time it was checked in or out)

    I see how doing it all on one sheet would be easier. It could also help remove copy paste errors that have been occuring. I am improving the excel sheet from what the person before me creates because his way of doing things actually leads to allot of error which has been creating problems for us. I think though the method of flagging items would be helpful.

    Each item when "flagged" as checked out to a shop, needs to be exported to a usage log in a new file which is seperate to each shop. It contains the time and date the item was checked out (which needs to change on the item at the moment it's checked out as well). The difference is, the useage log grows with each use, and the items never change. The times on the items we check out can change. Is there a way to automate this from a seperate sheet? I'm thinking it's going to have to be done with a macro
    Here's the problem with questions like "what would we see if we traveled faster than the speed of light". Since the rules that govern the universe as we understand them do not allow for such a possibility, to imagine such an event forces us to abandon those rules. But that leaves us no guide by which to answer the question. We have no idea as to what rules to replace them with, and we can't give an answer. - Janus
    Reply With Quote  
     

  6. #5  
    ▼▼ dn ʎɐʍ sıɥʇ ▼▼ RedPanda's Avatar
    Join Date
    Aug 2012
    Location
    UK
    Posts
    2,737
    What you are encountering is a common problem.

    People start using spreadsheets because they are a good way to hold tabular data.
    Then they start learning about the extra things spreadsheets can do (sorting / filtering / etc).
    Then they start learning about more advanced things (vlook-ups / reports / pivot tables etc).
    Then they start discovering all the things spreadsheets can't do - and start doing workarounds to get the result they want.
    These workarounds start failing and people get frustrated.
    At that point they realise they need bespoke software.

    I think you are currently in the middle of that list.
    I think you will probably need VLookUps and Pivot Tables next.

    But you are heading rapidly towards realising that you need bespoke software.
    SayBigWords.com/say/3FC

    "And, behold, I come quickly;" Revelation 22:12

    "Religions are like sausages. When you know how they are made, you no longer want them."
    Reply With Quote  
     

  7. #6  
     

  8. #7  
    Suspended
    Join Date
    Apr 2007
    Location
    Pennsylvania
    Posts
    8,797
    Excel spreadsheet template for lending library

    Search results for lending - Templates - Office.com
    Reply With Quote  
     

  9. #8  
    Forum Radioactive Isotope MagiMaster's Avatar
    Join Date
    Jul 2006
    Posts
    3,440
    To answer your original question, you can use COUNTIF to do what you want, at least for now. The syntax is =COUNTIF(Sheet2!A1:A8,"="&Sheet1!A1) Replace Sheet2!A1:A8 with the sheet and range containing your list of checked-out IDS and Sheet1!A1 with the cell containing your dropdown box.
    Reply With Quote  
     

  10. #9  
    Forum Bachelors Degree Shaderwolf's Avatar
    Join Date
    Dec 2005
    Location
    OPSEC, baby. Sorry.
    Posts
    425
    THANK YOU SO MUCH, you all really are helping me. Because I work for the organization I do, I can not download any new software onto these computers. It all has to be done in Excel.


    I found these two youtube videos:

    Excel Magic Trick 185 Dynamic Formula Extract Data 1criteria - YouTube
    Excel Magic Trick #187: Unique List w Dynamic Formula - YouTube

    I created flags to identify the location of each item. I am trying to combine the two ideas right now, so that I can have a dynamic sheet that will show me a list of each OC in an area and how many times it appears. I'll make it so that You can add a list of serials and how often they appear as well. The hardest part for me is to make sure that all of this is simple as possible so that even after I don't work here anymore next month the whole program can be intuitive, easy to use, and hard to mess up.

    It's good seeing you again MagiMaster and Harold by the way. Is Ophi still on here?
    Here's the problem with questions like "what would we see if we traveled faster than the speed of light". Since the rules that govern the universe as we understand them do not allow for such a possibility, to imagine such an event forces us to abandon those rules. But that leaves us no guide by which to answer the question. We have no idea as to what rules to replace them with, and we can't give an answer. - Janus
    Reply With Quote  
     

  11. #10  
    ▼▼ dn ʎɐʍ sıɥʇ ▼▼ RedPanda's Avatar
    Join Date
    Aug 2012
    Location
    UK
    Posts
    2,737
    Quote Originally Posted by Shaderwolf View Post
    The hardest part for me is to make sure that all of this is simple as possible so that even after I don't work here anymore next month the whole program can be intuitive, easy to use, and hard to mess up.
    A couple of other options that may help you achieve that are:
    Form Controls, Adding Interactivity to your Excel Worksheets | Chandoo.org - Learn Microsoft Excel Online
    Excel Data Entry Form Tutorial
    SayBigWords.com/say/3FC

    "And, behold, I come quickly;" Revelation 22:12

    "Religions are like sausages. When you know how they are made, you no longer want them."
    Reply With Quote  
     

  12. #11  
    Forum Bachelors Degree Shaderwolf's Avatar
    Join Date
    Dec 2005
    Location
    OPSEC, baby. Sorry.
    Posts
    425
    lol I actually have a little experience with form controls macros and the developer tab. I created a program for the last place I worked that would pull pictures out of a folder by their prescribed name, resize them, and fill a form with data pulled from a table. This project up to this point has proven a little more challenging, but the "flagging" method was a huge help. The only thing I'm pretty sure I won't be able to help at all is the fact that you can only use the file on one computer at once, so two people can't be checking stuff out at the same time. That's understandable though.
    Here's the problem with questions like "what would we see if we traveled faster than the speed of light". Since the rules that govern the universe as we understand them do not allow for such a possibility, to imagine such an event forces us to abandon those rules. But that leaves us no guide by which to answer the question. We have no idea as to what rules to replace them with, and we can't give an answer. - Janus
    Reply With Quote  
     

  13. #12  
    ▼▼ dn ʎɐʍ sıɥʇ ▼▼ RedPanda's Avatar
    Join Date
    Aug 2012
    Location
    UK
    Posts
    2,737
    Quote Originally Posted by Shaderwolf View Post
    The only thing I'm pretty sure I won't be able to help at all is the fact that you can only use the file on one computer at once, so two people can't be checking stuff out at the same time.
    Does the sharing still not work well enough?
    About shared workbooks - Excel - Office.com

    (I remember 'sharing' not being perfect - but the last time I tried sharing a workbook was back in 2003. It should have improved since then.)
    SayBigWords.com/say/3FC

    "And, behold, I come quickly;" Revelation 22:12

    "Religions are like sausages. When you know how they are made, you no longer want them."
    Reply With Quote  
     

  14. #13  
    Forum Bachelors Degree Shaderwolf's Avatar
    Join Date
    Dec 2005
    Location
    OPSEC, baby. Sorry.
    Posts
    425
    well, I was going to try to create something like that myself, but I realized something. You can scroll down and read the "resolving conflicts" area. I forsee the fact that those conflicts can exist, and that the only resolution is to choose who's changes to keep or not can create problems if we're going to use this in a proffessional environment where the people who will be using the program have little to no knowledge of excel, are lazy, don't communicate well, and tend to take shortcuts. Resolving the conflict as described would result in one person's work disapearing and the workers being to lazy to fix it. The whole idea of creating this program is making things as simple as possible, taking out any human error, and making "doing it the right way." the lazy and easy alternative to messing things up.

    If the sheet were to update as changes were being made, so you could see both people working at the same time it would be a different story. That time gap will create problems I just don't want to deal with.

    I think I've got enough information to make it work. The weekend's been a little crazy, but when I get back to work and finish it I'll let you know what all of your help ended up creating. I'll come back to this post with the results and any more questions. Thank you all for the advice so far.
    Here's the problem with questions like "what would we see if we traveled faster than the speed of light". Since the rules that govern the universe as we understand them do not allow for such a possibility, to imagine such an event forces us to abandon those rules. But that leaves us no guide by which to answer the question. We have no idea as to what rules to replace them with, and we can't give an answer. - Janus
    Reply With Quote  
     

  15. #14  
    ▼▼ dn ʎɐʍ sıɥʇ ▼▼ RedPanda's Avatar
    Join Date
    Aug 2012
    Location
    UK
    Posts
    2,737
    Quote Originally Posted by Shaderwolf View Post
    Thank you all for the advice so far.
    np

    It's been a while since I've had to do anything more complicated than sorting a column with Excel, so it was useful to revisit it.
    Last edited by RedPanda; September 2nd, 2013 at 04:52 AM.
    SayBigWords.com/say/3FC

    "And, behold, I come quickly;" Revelation 22:12

    "Religions are like sausages. When you know how they are made, you no longer want them."
    Reply With Quote  
     

  16. #15  
    Forum Bachelors Degree Shaderwolf's Avatar
    Join Date
    Dec 2005
    Location
    OPSEC, baby. Sorry.
    Posts
    425
    I like it. Figuring all of this stuff out is actually allot of fun.
    Here's the problem with questions like "what would we see if we traveled faster than the speed of light". Since the rules that govern the universe as we understand them do not allow for such a possibility, to imagine such an event forces us to abandon those rules. But that leaves us no guide by which to answer the question. We have no idea as to what rules to replace them with, and we can't give an answer. - Janus
    Reply With Quote  
     

  17. #16  
    Forum Radioactive Isotope MagiMaster's Avatar
    Join Date
    Jul 2006
    Posts
    3,440
    Quote Originally Posted by Shaderwolf View Post
    It's good seeing you again MagiMaster and Harold by the way. Is Ophi still on here?
    I only got back on fairly recently myself, so I don't really know.
    Reply With Quote  
     

  18. #17  
    Suspended
    Join Date
    Apr 2007
    Location
    Pennsylvania
    Posts
    8,797
    Quote Originally Posted by Shaderwolf View Post
    It's good seeing you again MagiMaster and Harold by the way. Is Ophi still on here?
    Welcome back. Ophi is here, posting under the handle "John Galt."
    Reply With Quote  
     

Similar Threads

  1. MS excel VS origin
    By bobby2009 in forum General Discussion
    Replies: 0
    Last Post: March 29th, 2010, 01:43 AM
  2. .asp to excel -- get data
    By ohalligan in forum Computer Science
    Replies: 0
    Last Post: March 10th, 2010, 05:04 PM
  3. Excel graph
    By Lightz in forum General Discussion
    Replies: 0
    Last Post: February 25th, 2010, 02:58 PM
  4. Excel, VBA
    By Rhapsody_ in forum Computer Science
    Replies: 1
    Last Post: November 25th, 2009, 09:05 AM
  5. Excel speed problem
    By newnothing in forum Computer Science
    Replies: 0
    Last Post: March 1st, 2009, 01:45 AM
Bookmarks
Bookmarks
Posting Permissions
  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •