Help with linking data validation list and functions on multiple sheets.

[cmcglli]

Hello

I am making a maintenance sheet for work and I want to make it so when someone selects YES or NO from a drop down menu on sheet one it chances the amount of parts or left on sheet two.
I already completed sheet one but I am not sure how to make it so it changes the amounts on sheet two every time someone does a service.

for example:
Sheet 1
Were O-Rings changed YES----selected from drop down menu

Sheet 2
211 O-Rings 200-----amount left after selecting yes

Thanks to all that reply!

[K m]

Do you have an example workbook?

[protonLeah]

To Attach a Workbook:
1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit

[cmcglli]

Here is a sample sheet.

I want to make it so when YES is selected on sheet 1, a quantity of 2 is taken off from sheet 2 every time someone uses the sheet.

[protonLeah]

here's one:

[cmcglli]

Thats PERFECT! What are the steps to complete this?

[protonLeah]

There are two macros in the workbook:

Option Explicit

Private Sub Worksheet_Activate()
    Range("h2").Value = "NO"
    If Sheets("hardware list").Range("D2").Value = 0 Then
        MsgBox "No 211 O-Rings available"
        Range("E2").Select
    End If
End Sub

The above changes the dropdown selection to NO when ever the worksheet is activated. It checks the hardware list sheet to see if the count is zero. If it is, then it moves the cursor to the adjacent cell so that the dropdown cannot be selected and gives a zero count message.

The code below is triggered whenever there is a change in check list sheet. If the change was triggered by a change in the dropdown cell H2. If it's YES, and the hardware list sheet is more than zero and subtracts two from that value.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) = "H2" And Target.Value = "YES" Then
        If Sheets("hardware list").Range("D2").Value > 0 Then
            Sheets("hardware list").Range("D2").Value = Sheets("hardware list").Range("D2").Value - 2
        End If
    End If
End Sub

You can view the code by right clicking on the check list tab and selecting View Code.