Inventory Spreadsheet

mmickells

New Member
Joined
Mar 28, 2019
Messages
2
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.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
.
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.

Code:
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
 
Upvote 0
@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.
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top