Free Microsoft Excel 2013 Quick Reference

Excel for Inventory Cycle Counting

I have an inventory list of about 750 items broken down into 7 categories. Essentially I would like to have excel pick 15 random items per week for us to count. The important thing is it only picks each item number one time so they don't get counted twice.

What is the best and simplest way to go about doing this? Is there a way to show which items have already been selected.

All suggestions are greatly appreciated.


Post your answer or comment

comments powered by Disqus
I am looking for help getting a cycle count program started and was hoping
that someone had some suggestions for ways to maintain this using an excel
spreadsheet format. My companies software does not have the cycle counting
feature as part of the general WMS functionality. Thanks for any suggestions
or links that can be provided.

Hi All,

Completely new user here, been driving forklift for several years now and attrition left a vacancy in the front office, so I stepped up. I have a decent grasp of basic Excel functionality, but there's a pickle I'm in and have been trying to spitball a solution to, with little luck. Maybe you can help!

I work for the fulfillment arm of a large marketing company, which means we have quite a few promotional items in and out every day. We recently took over a competitor's client, and needless to say, they're being less than helpful when it comes to providing an accurate pack list for about 40 pallets of goods we just received. I've got a team going through each pallet individually and have a spreadsheet going to at least match the declared number of items to the actual count, and conditional formatting colorizing results so we can at least get an at-a-glance feel of how close we are. Here's the sticking point: Usually we would just use some inventory management software, but all we have relies on Excel, the industry standard. Since it's up to me to get basically 140 pages of word doc into excel format so we can load the webstore, my problem is twofold:

1) Is there any way to use Excel to lump multiple boxes of similar items (we have everything currently sorted by item number, each as its own row) on different pallets without creating a new entry for each item?

and, what I'm really dreading

2) Does anyone have any experience managing inventory with excel? The hope is that we'll get everything in the other software before back orders come in, but there arises the possibility I'm going to have to place and track these pallets in our warehouse while pulling items individually for shipment and keeping my counts accurate.

I'm insanely new at the logistics side of this, any and all help would be much appreciated. I use Excel 2007.

I am looking for an EXCEL template or formulas to calculate inventory shelf
life by days. Something like an accounts receivable aging report. Is this
possible? If so, how?

Hey all,

I'm new to the forum but I have been reading post for a very long time.

I have decided to change the way we do cycle counts to get more of a flow. I'm just now learning about Visual Basic in Excel so I'm looking for some pointers.

I pull data into excel which includes last date cycle counted in A2. We use VOL rules with V being the fast moving product and L the slowest. I have the VOL split into a sheet of there own. V's we count 4 times a year. O's twice a year and L's one time a year.

I'm using networkdays to find how many working days we have in a quarter. I want the program to take the Last Cycle Count date - Today's date and if the sum => 1st quarter it will copy that row to a new sheet. This would be on the V's of course. Each quarter it would need to look at the next quarter for updated days.

The same would apply for O's and L's just using a little different =>

I'm having to use text to columns each time I refresh data because the date is coming in looking like 20111112. We are using an old AS400 system.

Anyway thanks for any help offered. Visual Basic seems to be very powerful and I'm sure I will figure it out once I read a few books.

Excel Subotal Function. Sum, Count etc only the visible cells after ...

Excel Subotal Function. Sum, Count, Average etc only the visible cells after filtering. ... Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ...

2 Excel Functions/Formulas to Count/Sum Excel Cells by Color - Excel ...

Custom Excel Formula to count and or Sum by color ... Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only $145.00 . $59.95 Instant Buy/Download ...

Hi Guys,

Just wondering if someone could help me ...

I had a macro that worked perfectly in Excel 2010 however have recently changed over to a MacBook Pro and the macro is coming up with an error when it is ran in Excel for Mac 2011.

The "Update Lists" button in the top right hand corner on the Master Database tab is what runs the macro.

Option Explicit

Sub ParseItems()
'Author:    Jerry Beaucaire
'Original Date:    11/11/2009
'Summary:    Based on selected column, data is filtered to individual sheets
'        Creates sheets and sorts sheets alphabetically in workbook
'        6/10/2010 - added check to abort if only one value in vCol
'        7/22/2000 - added ability to parse numeric values consistently
Dim LR As Long, Itm As Long, MyCount As Long, vCol As Long, pCol As Long
Dim ws As Worksheet, MyArr As Variant, vTitles As String, Oops As Boolean

Application.ScreenUpdating = False

'Column to evaluate from, column A = 1, B = 2, etc.
   vCol = 1     'column A for division
   pCol = 15    'column O for status
   
'Sheet with data in it
   Set ws = Sheets("Master Database")

'Range where titles are across top of data, as string, data MUST
'have titles in this row, edit to suit your titles locale
    vTitles = "A9:Z9"
   
'Spot bottom row of data
   LR = ws.Cells(ws.Rows.Count, vCol).End(xlUp).Row

'Get a temporary list of unique values from column A
      ws.Columns(vCol).SpecialCells(xlConstants).AdvancedFilter _
        Action:=xlFilterCopy, CopyToRange:=ws.Range("EE1"), Unique:=True

'Sort the temporary list
    ws.Columns("EE:EE").Sort Key1:=ws.Range("EE2"), _
        Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, _
        MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

'Check for more than one value in list
    If ws.Range("EE" & Rows.Count).End(xlUp).Row > 2 Then

'Put list into an array for looping
'(values cannot be the result of formulas, must be constants)
        MyArr = Application.WorksheetFunction.Transpose(ws.Range("EE2:EE" _
            & Rows.Count).SpecialCells(xlCellTypeConstants))

'clear temporary worksheet list
        ws.Range("EE:EE").Clear

    Else
        ws.Range("EE:EE").Clear
        Oops = True
        GoTo ErrorExit
    End If
    
'Turn on the autofilter, one column only is all that is needed
    ws.Range(vTitles).AutoFilter

'Loop through list one value at a time
'In case values are numerical, we convert them to text with ""
    For Itm = 1 To UBound(MyArr)
        ws.Range(vTitles).AutoFilter Field:=vCol, Criteria1:=MyArr(Itm) & ""
        ws.Range(vTitles).AutoFilter Field:=pCol, Criteria1:="<>Inactive"
    
        If Not Evaluate("=ISREF('" & MyArr(Itm) & "'!A1)") Then    'create sheet if needed
            Sheets("Regional Staff").Copy After:=Worksheets(Worksheets.Count)
            ActiveSheet.Name = MyArr(Itm) & ""
        Else                                                      'clear sheet if it exists

        End If
        
        With Sheets(MyArr(Itm) & "")
            .Range("B10:N" & .Rows.Count).Clear
                
            If ws.Range("A" & Rows.Count).End(xlUp).Row > 10 Then
                ws.Range("B" & Range(vTitles).Row + 1 & ":N" & LR).Copy .Range("B"
& .Rows.Count).End(xlUp).Offset(1)
                
                .Range("B9:N12").Sort Key1:=.Range("B10"), Order1:=xlAscending, _
                                      Key2:=.Range("C10"), Order2:=xlAscending, _
                                      Header:=xlGuess, OrderCustom:=1, _
                                      MatchCase:=False, Orientation:=xlTopToBottom, _
                                      DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
            End If
            MyCount = MyCount + .Range("B" & .Rows.Count).End(xlUp).Row - Range(vTitles).Row
        End With
        
        ws.Range(vTitles).AutoFilter Field:=pCol
        ws.Range(vTitles).AutoFilter Field:=vCol
        
    Next Itm
    
'Cleanup
    ws.AutoFilterMode = False
    MsgBox "Rows with data: " & (LR - Range(vTitles).Row) & _
        vbLf & "Rows copied:     " & MyCount & _
        vbLf & "Inactive Rows:   " & Application.WorksheetFunction.CountIf(ws.Range("O:O"),
"Inactive") _
        & vbLf & vbLf & "Hope they match!!"

ErrorExit:
    If Oops Then MsgBox "Only one value found, aborting parse process..."
    Application.ScreenUpdating = True
End Sub


Hi,

This relates to Excel for Mac 2004

I'm trying to use Application.FileSearch in a procedure to open files in a folder one by one and then do some stuff. It works fine when I developed it on my Windows machine but won't work on the Mac platform. The culprit instruction seems to be the

Application.FileSearch instruction, and I've also tried what I think may be a Mac equivalent Application.FileFind, but I get the 'Object doesn't support this action' error.

So I wrote a simple procedure below just to test the .FileSearch instruction but this fails in the same way.

Any ideas anyone? I've commented out lines which relate to Windows syntax.

Sub test()
   ' With Application.FileSearch 'Windows
   With Application.FileFind ' Mac
       '.Options = msoOptionsNew
       .SearchPath = "Macintosh HD" ' mac
       '.LookIn = "C:Temp" ' Windows
       .SearchSubFolders = False
       .Execute

       MsgBox "Number of files found " & Str(.FoundFiles.Count)
       
   End With

End Sub

Usual TIA


I used to use Excel for Windows, and then switched to Mac (Office 2008). One feature seems to have disappeared -- when I highlight a cell that contains a formula, in the old version it used to lightly highlight the cells referenced in the formula. That way, if it was a simple SUM function for example, I can immediately see which cells were being counted. Now, it doesn't highlight... I have to look at the actual formula and refer to the column or row numbers themselves. OR, I can hit Ctrl-U to edit the formula, and only then are the cells highlighted.

I'd love it if the cells would highlight by default, without requiring me to be in edit mode. Is this possible?

Thanks.

Hi guys,
I've scoured the web and suggestions are to modify the status bar, or to insert a form.

But I have Excel for Mac. "StatusBar =" doesn't generate an error, but doesn't change the status bar.

And I can insert a form, but the required properties don't seem available, so I think all the posts I'm finding are for VBA in Windows.

Could anyone point me to a routine that would work in excel for mac? (Ideally a progress indicator bar, but I'll take modifying the statusbar too).
Thanks :-)
MW

I have been trying to find a way to use Excel for simple floor plans. We have sales guys that take shop measurements - one of them stated that you can use Excel for these simple floor plans and I have tried to find via online with no success. Any help greatly appreciated as it would assist these guys out in the field without having to wait for a cad drawing.

drawing tools (like in excel) for an access form...

i would like to with some drawing objects in an access form like i do in an excel sheet (with the drawing toolbar)...?

thank you...

Excel Developer Tip: Cell Counting Techniques

Counts the number of empty cells in a range. The COUNTIF function. Very flexible, but often not quite flexible enough. An array formula. Useful when the other techniques won't work.

Excel Developer Tip: Cell Counting Techniques

Excel page. Tip archives: Cell Counting Techniques. Excel provides many ways to count cells in a range that meet various criteria: The DCOUNT function.

How to access the data analysis tools in Excel for Mac

The tools use the appropriate statistical or engineering macro functions, and then display the results in an output table. Some tools generate charts and also output tables.

How to use dates and times in Excel for Mac

To help you perform complex date and time calculations, Microsoft Excel includes many built-in date and time functions. Back to the top

Hi guys

- debug step through suddenly stopped working. It runs the whole macro, gives me an error (e.g. type mismatch), but won't show me where.

I opened another macro that was fine before and same thing, so it seems specific to my installation of Excel for Mac, not to the macro.

skips through breakpoints as if they weren't there.

I have "break on unhandled errors" checked, and even tried "break on all errors".

Any help appreciated.

Problem
SQL. REQUEST Function in Excel for Oracle restricts all but the most basic SQL statements. For example, you cannot use the left/right/mid & sum functions. The same SQL statement works in MS Access but not in Excel for Oracle (the problem does not exist with MS SQL environment).

e.g. "SELECT left(project,4) from FYTD" will be rejected by the function

Any idea if this is a syntax issue or of the SQL.Request function for Oracle is just that limited?

System:
Windows XP
Excel 2002
Oracle client ODBC driver
Oracle 8.1

Financial Formulas and Functions in Excel for Investment, NPV ...

List of Finance Functions and Formulas in Excel for Financial Modeling including depreciation, investments, NPV, interest and more

All,

I'm trying to create a report for a coworker to run. I want to filter the parameter in a cell (ssn), so that she can run the report. I have added a button for her to execute..once she puts in teh ssn. The spreadsheet is linked to MS Query in the background. How do I tell the Query to link to the cell in excel for filtering?

Many thanks.

In Excel for Mac (2004), the highlighted cell is shown by a pale blue outlining (pale blue shading when multiple cells highlighted) - incredibly difficult to see.

I can't find any setting to change this anywhere - am I missing something obvious?

Thanks

Hi,
How do I store frequently used text in Excel for auto-complete?

how to get alert message when window is closed in excel for blank cells

Is there any advantage to using Access over Excel for a simple database
containing names, addresses, phone numbers and basic biographical
information? I will eventually want to create mailing labels from the
database and extract email addresses to send group emails.


No luck finding an answer? You could always try Google.