Inventory Spreadsheet

[mmickells]

I've inherited an Excel document that is used to keep track of where materials are located within out facility. Recently they asked me to include quantity counts and mark them red when quantity gets low. This part wasn't hard, simply used a <count-if> function, then set conditional formatting based on threshold.

We'd like to move the quantity counts to another tab (Dashboard) which isn't a big deal. However, a couple of other request is over my head:

• Would it be possible to include the locations of that part number that are low on the dashboard?
• Would be possible to send an email when an inventory count reaches a certain point?

I'm not 100% I've explained this well, but am more than happy to answer any questions.

 

[Logit]

.
This macro is located in the Sheet 1 module. It checks if the number in a specific cell drops below a specified level. When that occurs it automatically runs the email
macro located in a routine module. It can be modified for your purposes.

The other question re: location of part number that is low in quantity on the dash board sheet .... yes that can be done. Just a matter of plugging it into your data
during the design phase and insuring the information is updated as changes are made.

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
'Do nothing if more than one cell is changed or content deleted


   If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub


      


    If Target.Address = "$H$3" Then


       If Target.Value < 600 Then


            'Stop any possible runtime errors and halting code


            On Error Resume Next


                'Turn off ALL events so the Target * 2 does not _


                'put the code into a loop.


                Application.EnableEvents = False


                EmailNotice
                
                'Turn events back on


                Application.EnableEvents = True


            'Allow run time errors again


            On Error GoTo 0


        End If


    End If


End Sub

Download workbook : https://www.amazon.com/clouddrive/share/iAIxiVNXuSUxmcz8pr8xu8YUIo2ET1GmPc2WnNCp3v

 

[mmickells]

@Logit - Thank you for your help! I'm taking a look at the Excel sheet you've attached. I may reach back out if I have questions. Hope you enjoy your weekend.