+ Reply to Thread
Results 1 to 15 of 15

Inventory Tracker Question

  1. #1
    Registered User
    Join Date
    12-03-2010
    Location
    Springfield, Kentucky
    MS-Off Ver
    Excel 2010
    Posts
    9

    Inventory Tracker Question

    I am working on an inventory tracking workbook for our organization and am looking to solve a few questions. My main question would be this, is there any way to script out, or use a macro to find totals using variables so to speak.
    We will have a sheet (see attached) with machine information and schools in which the machine is located and what type of machine it is.
    For instance one machine could be located at the high school, be a student computer, and it would be a laptop, in a lab environment. What i would like to do is be able to grab totals for each on a dashboard like frontend so we can see this at a glance so it would look something like "High School, Student, Lab, Laptops = 156" for example.
    The main reason for doing this is for reporting at the end of the year to the state. Will make life so much easier if this is possible.
    We will mark the variables with ones so we can get totals on laptops, desktops, ect...

    That is question one of like 5. Would like to solve these one at a time.
    If you need anything cleared up or more explanation just let me know.
    (attached version is not the final revision of the document)
    Attached Files Attached Files
    Last edited by shmew22; 12-03-2010 at 12:29 AM.

  2. #2
    Forum Contributor
    Join Date
    07-05-2006
    Location
    Canada
    MS-Off Ver
    2003, 2007
    Posts
    581

    Re: Inventory Tracker Question

    Hi,

    I'd recommend trying out the Excel Table feature. The creation of an Excel Table is done by selecting Insert -> Table from the Ribbon. Then highlight the section you would like to create the table for. (In your case, it would be cells A6 to X7.)

    This Table can then be renamed for easier reference for other formulas. In the Ribbon, if you've got a cell in the Table selected, there's the option to look at the Table Tools. If you select that, on the left there is a properties section with a field called Table Name. It is probably more convenient to change the name to "Inventory" or something else that is easy to remember. For the purposes of this example, I'll call it "Inventory".

    With the Table created, the important thing to remember is when you enter new information, it needs to be on the immediate next row in order for the Table to auto-expand. (On the reasons I'm suggesting a Table)

    Now, in order to do the calculations you wanted to do, I would recommend the use of some of the less obvious features of the function SUMPRODUCT. For an explanation of how these features work, please refer to this article -- http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- it's a bit of a read, but hopefully you'll find it quite enlightening.

    So, to do the calculation you specified in your example, you would put a formula like the following into Dashboard!B4:

    PHP Code: 
    =SUMPRODUCT(--(Inventory[Student]=1),--(Inventory[Lab]=1),--(Inventory[WCHS]=1),--(Inventory[Laptop]=1)) 
    What this function does is count every instance where the field mentioned equals the value that is indicated. In your example, you used 1's for these fields, so this looks for 1's. There are other alternatives. For example, for the Student portion above, you could instead put:

    PHP Code: 
    ..... --(Inventory[Student]<>"") .... 
    This would consider all instances where the Student field is not blank.

    The possibilities are quite endless -- so I'll leave you to determine what questions you still need answered. I've attached your file so that it corresponds to what I've said.

    Hope this has been helpful.

    S
    Attached Files Attached Files
    ------------------------------------------------------------------------------------------
    If you need no more help on the current problem, please mark it as "Solved". It saves time
    as many of us will look at threads if they are not marked as "Solved".

    The instructions on how to do this are found in the Forum Rules thread that is at top of every forum.
    (Currently you'll have to look at point #9.)
    ------------------------------------------------------------------------------------------

  3. #3
    Registered User
    Join Date
    12-03-2010
    Location
    Springfield, Kentucky
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Inventory Tracker Question

    you sir or madam, are my hero. Thank you so much.

    Second Question: We will also need totals on our dashboard page with how many machines have a certain operating system. Is there a way to pick out xp machines or win 7 machines from the list and have it total based on what value is in the cell?

    reuploaded with added column.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    07-05-2006
    Location
    Canada
    MS-Off Ver
    2003, 2007
    Posts
    581

    Re: Inventory Tracker Question

    Ah, for that you get to explore another one of Excel's features, the Pivot Table. If you do end up using this, please pay careful attention to the bit on Refreshing at the very bottom.

    If you click somewhere in the table, then click on Table Tools in the Ribbon, there's an option that is called Summarize with Pivot Table (it's second from the left in my ribbon, not sure how it will show in Excel 2010). A PivotTable is quite robust for showing summaries for your data.

    So to create the Pivot Table, you would click Summarize with Pivot Table while the table is selected. Your first pop-up will ask where you want to get the data from and should have "Select table/range" already selected with your table "Inventory" in the blank. You would then hit Ok. That will pop a blank Pivot Table into a new worksheet.

    [INTERLUDE] Here are some links you may consider looking at to have Pivot Tables explained in more detail. I'd recommend that you read up on them if you've never used them before.

    http://www.contextures.com/CreatePivotTable.html -- Describes how to set up the data in the Pivot Table and rearrange things. You could skip the section on Creating a Pivot Table and go directly to Adding Fields to a Pivot Table. If you look at the bottom, she has a several other topics (I believe most of them are set up with Excel 2003 in mind, but still useful).

    http://office.microsoft.com/en-us/ex...aspx?section=1 This is a small tutorial put together by Microsoft. I only briefly looked over the contents, but you might find it helpful.

    [END INTERLUDE]

    For your particular question, you would put OS in as a Row Label. You would then need to add something to the Sum Values (it uses the Summation symbol in Excel, wasn't sure how to type it here). I added S/N and specified that it should do a Count of that. For doing a count such as this, you should use a field that will always have a value, I assume S/N is such a field. After doing this, you'll get a result like that in the file attached.

    In general, to work with arbitrary questions, Pivot Tables are great, but they do have a bit of a learning curve when you first get started.

    Now back to the point I mentioned before, Refreshing! Pivot Tables do not automatically update. This prevents constant recalculation. So you need to ensure that when you go to use the Pivot Table that you Refresh the data. This can be done in two ways:
    1. Click on the Pivot Table, then right click and choose "Refresh".
    2. Click on Pivot Table Tools in the Ribbon, choose "Refresh" (it's in the middle of the screen). Note: There are two Pivot Table Tools tabs, one called Options and one called Design -- this would be on the Options tab.
    S
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-03-2010
    Location
    Springfield, Kentucky
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Inventory Tracker Question

    Awesome... Exactly what i was wanting to do. Thanks again... now for number 3. The state has certain criteria that defines a pc as meeting standards. What i need now is a way to define whether a pc meets standards. The standards for a pc are 512mb of ram, 1.5ghz cpu, 50gb hard drive. In this situation i would have to be able to assign a value to the type of processor. so i5 would be 2.5 in our case and so on... And then taking all of these values into account have a section to total up how many machines dont meet these standards. Is this possible using the same SUMPRODUCT formula?

  6. #6
    Forum Contributor
    Join Date
    07-05-2006
    Location
    Canada
    MS-Off Ver
    2003, 2007
    Posts
    581

    Re: Inventory Tracker Question

    Ram & HD Capacity

    With respect to the Ram and HD Capacity, it's fairly easy. Simply indicate that the Ram column is in Megabytes and the HD Capacity column is in Gigabytes, then only put the numbers in the column. (ie. put in 250 if the HD Capacity is 250 Gb) In this way, you can do a SUMPRODUCT with the following bit:

    ... --(Inventory[Ram]>=512), ... --(Inventory[HD Capacity]>=50) ...

    It is important you keep in mind the units everywhere, but as long as you're consistent, and just use numbers (no text), you should be fine. There is one additional thing that you can do. Assign the following custom number formats:

    Ram column: #,##0 [$Mb]
    HD Capacity column: #,##0 [$Gb]

    This should provide the implicit reminder as to which units need to be used. Keep in mind that 1 Gb of Ram = 1,024 Mb of Ram, so you'll need to multiply anything in Gb for Ram by that in order for things to work. If you ever get to the point where the standard is 1 Gb, you could change the units that are used.

    Processor Speed

    How particular are you going to be about the processor? Depending on the i5 processor (ie. i5-520M - 2.4 GHz, i5-750 - 2.66 GHz), it can vary somewhat. Are you actually going to list the specific processor (ie. i5-520M) or are you just going with the processor class (ie. i5) and an approximation of the speed (ie. 2.5 GHz), or are you intending to specify the processor class (ie. i5) and specify the speed for the specific processor in a separate column (ie. 2.66 GHz)?

    Depending on how you are planning to do things will be useful to determining a reasonable method.

    I would personally recommend the following:
    1. Insert a Table for Processors (in the sample provided in the Processor tab... the Table is named Processors).
    2. After this, you need to insert a named range called ProcessorList as follows: =Processors[Processor]
    3. Then, I'd recommend putting a dropdown in the Processor column in the Inventory table that utilizes the ProcessorList.
    4. Finally, I'd recommend putting an additional column in the Inventory table called Proc Speed with the following formula:

    Please Login or Register  to view this content.
    Ya... a bit of effort. :-) The result of this is that you are able to add the following section to your SUMPRODUCT:

    ...., --(Inventory[Proc Speed]>=1.5), ...

    The other thing you can add is a custom format:

    Proc Speed column: #0.0 [$GHz]

    S
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    12-03-2010
    Location
    Springfield, Kentucky
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Inventory Tracker Question

    I'm literally amazed at how much of this excel can actually do! I am extremely grateful for all of your help on this thus far, and i am learning alot during this process. Feeling like an excel junkee now. :-) I am going to put these methods you have described to work and i will repost the workbook for you to have a look at and see what you think. I may have some more questions about this as i implement them. Thanks so Much!

  8. #8
    Registered User
    Join Date
    12-03-2010
    Location
    Springfield, Kentucky
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Inventory Tracker Question

    I have hit a snag in our reporting. I need to be able to filter per school how many machines will have been virtualized out. (using thin clients) I can do this per each individual school by using the SUMPRODUCT formula from eirlier. However i need to combine the elementary schools into a total, and middle/high schools into a separate total. In addition i need to have the total number of thin clients (nodes) that will be in each school. I thought about doing a separate page to do the calculations for this and hiding it so that the dashboard can pull from this and we wont end up messing up that sheet. Any suggestions? I have attached what i have gotten so far so you can see what i mean.
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    07-05-2006
    Location
    Canada
    MS-Off Ver
    2003, 2007
    Posts
    581

    Re: Inventory Tracker Question

    Hmm, you might want to adjust how you're storing data slightly to make it a bit simpler. The example attached, I set up a table similar to what was done for Processors, except called it Schools. Then I inserted two columns into the Inventory table, one for School, and one for School Category. Similar to Proc Speed, the School Category column is just a formula that depends on the Schools table. The dropdown for School was set up the same way the one for Processors was.

    (Note: I'm assuming a computer can only be attached to one school at a time)

    What this means is you would need to adjust your formulas slightly when choosing data specific a particular school. Whereas before you would have put:

    ...., --(Inventory[WCES]=1), ....

    you would instead need to put the following:

    ...., --(Inventory[School]="WCES"), ....

    or alternatively, you can refer to a cell as you'll see it does in the sample provided for cells B20:B23. [ASIDE]: I didn't do it the same for B24 as your formula there is different, your signs are completely opposite; my opinion is sort of vague about this cell, if it was intentional I'm not sure you're doing what you think you are doing) [END of ASIDE]

    Similarly, to test for Elementary or Secondary, you can put:

    ...., --(Inventory[School Category]="Elementary"), ....

    So, that's probably what I would do for this, if the assumption I made is accurate. (Also, doing it this way, you could remove the 5 columns from the table, as shown in the sample.)
    _____________________________________________________________________

    With respect to your actual question, it is possible to adjust the SUMPRODUCT to represent an OR situation, such as wanting to see something for i3 or i5 processors (but none of the others). That particular statement would be set up as follows: (Note the removal of the -- from the front)

    ...., (Inventory[Processor]="i3")+(Inventory[Processor]="i5"), ...

    In your original situation with a column for NWES, WCHS, etc, you can do something similar, but you need to be careful. In the below example, if you should happen to have a "1" under both NWES and WCES for one of the computers, the following would count 2 machines instead of just 1. This is why I'd recommend just having one column for School as it implicitly makes it so a person can't check off two separate schools for the same machine.

    ...., (Inventory[NWES]=1)+(Inventory[WCES]=1), ....

    This sort of thing (with the plus sign) is best done with values that are all in the same column, as would be the case in the suggested setup for the School column.

    S
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    12-03-2010
    Location
    Springfield, Kentucky
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Inventory Tracker Question

    I see where your coming from on that. Much more logical that way. So another question pertaining to this same situation, instances where a machine has a virtualization card installed with 3 nodes attached (for example) would all have to total as 4 machines (1 host,3 nodes) for the report to be accurate. So it complicates the situation further... :-/ My concern at this point is being able to add that in with normal workstations. Not sure how clear this is... So if you dont understand i dont blame you in the slightest. Just ask me if u need some more clarification... Thanks so much again

  11. #11
    Forum Contributor
    Join Date
    07-05-2006
    Location
    Canada
    MS-Off Ver
    2003, 2007
    Posts
    581

    Re: Inventory Tracker Question

    How do you contain the virtualization information? Is it just the Nodes number? Do any other fields relate to that, ie. nComputing Host, 3 or less, 4 or more?

    (Oh yeah, with respect to the file attached in the previous message, I was messing around with the formula in cell B20 (it's currently incorrect); the formula from cell B21 would need to be copied to B20.)
    ____________________________________________________________

    Other questions:

    The District field, is this uniquely determined by what school is selected? If so, that could be added to the School table so it wouldn't need to be entered in general.

    Other fields should possibly be combined, similar to the school one. The following suggestion does not need to be followed; in my opinion, the following changes will make for fewer mistakes when inputting information (such as accidentally putting two 1's on the same line for something). I don't know what unique situations you will be attempting to capture, so it is a decision you will need to make. Important: These opinions only apply if the values in the fields are mutually exclusive, that is... a Laptop can't be a Desktop... the computer can't be in a Lab and a Classroom at the same time ... and so on.
    • Laptop, Desktop -> Field called Computer Type (or similar) with those two options. It allows you flexibility if your school system should purchase other types of computers.
    • Lab, Classroom -> Field called Location (or similar) with those two options. Again, allows more flexibility, such as if you need an options for Office, Library, etc.
    • Student, Teacher, Admin -> Field called User Type (or similar) with those three options. This one seems to be the most likely not to be quite as simple as I'm stating. If your virtualization functions according to the picture I have in my head, you could have Teachers and Students using the same machine?
    • 3 or less, 4 or more -> These potentially should be combined -- not clear exactly what they do.
    How does the User field relate to things? Does only one person use these computers, even in situations where you're counting them as multiple machines due to the virtualization?

    S

  12. #12
    Registered User
    Join Date
    12-03-2010
    Location
    Springfield, Kentucky
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Inventory Tracker Question

    the 3 or less and 4 or more fields are for the kets reporting section of the dashboard, it asks for how many machines are in rooms with 3 or less machines or 4 or more. The district field is where we will enter our district codes (asset tags), they are unique numbers that are only attached to one machine. In the user field we put the teacher's name so that we can easily search for what machines are in a teacher's room as not all of them have a room number.

    The information for the virtualization is only contained in the Nodes and Host columns. So if we have a machine that is a host computer we will mark that column and then we will put in the nodes column how many clients are connected to that host. So instead as counting the host as one machine, we have to count is as multiple machines depending on how many nodes are connected.

  13. #13
    Forum Contributor
    Join Date
    07-05-2006
    Location
    Canada
    MS-Off Ver
    2003, 2007
    Posts
    581

    Re: Inventory Tracker Question

    In that case, I would combine the 3 or less and 4 or more columns into one column called 4 or more. Then, you just need to put a Yes or No value in that column. Whenever it's no, then you know it's 3 or less. You could add data validation to the column so that only Yes or No can be chosen from a drop down.

    The attached spreadsheet does everything mentioned above (each bit was done in a similar fashion to what was previously mentioned, it was just done 5 or 6 more times to create each extra table). If you look on the Inventory tab, I've color coded what was done to each of the columns.
    • Orange columns: A formula, so you shouldn't need to enter values.
    • Blue columns: Added data validation to restrict the values that are entered to values that are appropriate to the field (Should lead to better data in general)
    • Green columns: The data in these columns is restricted (using data validation) to the Lists that are contained in the Lists of Values tab. If you need to be able to choose more possible values, you just need to add them to the appropriate table in this tab.
    The validation, particularly on fields you're evaluating (such as the OS) will make it so you don't get accidental typos. The one change made is I moved the Processors table into the Lists of Values tab so they're all in one place. The only place there isn't currently validation of any sort is for Warranty. It could fairly easily be added to that column too. I would recommend it if you're actually going to be reporting on that field at any point.

    Note: You can remove the coloring, they're just there for your info.

    S
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    12-03-2010
    Location
    Springfield, Kentucky
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Inventory Tracker Question

    Ok, that certianly makes sense to enter data in that fashion. But im still having trouble calculating how many nodes are at each location. My supervisor would like to keep the spreadsheet in the manner that it is now with the ncomputing (virtualization) information on a separate sheet as it is now. so my question now is, how do i get the total number of nodes at each school to show on this separate sheet?
    Attached Files Attached Files

  15. #15
    Forum Contributor
    Join Date
    07-05-2006
    Location
    Canada
    MS-Off Ver
    2003, 2007
    Posts
    581

    Re: Inventory Tracker Question

    To count the nodes...

    If you're using the modified layout in my previous suggestion, the following formula would do it:

    PHP Code: 
    =SUMPRODUCT(--(Inventory[Type of User]="Student"),--(Inventory[Location]="Classroom"),--(Inventory[School]="WCES"),Inventory[Nodes]) 
    If you're using your original layout for data, the following formula would do it:

    PHP Code: 
    =SUMPRODUCT(--(Inventory[Student]=1),--(Inventory[WCES]=1),--(Inventory[nComputing Host]=1),Inventory[Nodes]) 
    S

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1