How to implement changing the value of a cell in one sheet when changing the values ​​of another cell in another sheet?

Question

excel

I have an Excel workbook with two sheets. On one sheet there is a list with addresses (the total number is more than 100) and the number of units on it (picture ). The second one (a list of people with more rows than addresses) has a column with the address number among other data. How to make it so that when choosing the number of one or another address on the second sheet, the calculation is made in + or - on the first, respectively?
For example, on the second page in one of the lines I select address No. 38 and on the first sheet the field decreases for this address by 1, if I choose a different address number, then accordingly it decreased in the newly selected one, but here it increased.
That is, if on the second page in any of the lines in the address column I enter or select from the drop-down list one of the address numbers that are indicated on the first page, on the first page in the quantity field the value changes accordingly. If the value in the quantity field is 10, then in 10 lines of the second sheet I need to indicate the corresponding address.
How can this be implemented? Perhaps a little confusingly explained. Thanks in advance everyone!

Answer 1

This is done via COUNTIF (account if).
// corrected response //
See picture. I recommend opening it separately so that you can see it better.
Comments:
1. How best to structure the table is an open question.
For example, through validation in the "People" table, you can make it so that you can not enter the address manually or, even worse, its number (identifier, index, in fact) - but choose it from a convenient drop-down list that is populated with values ​​from the table " addresses". You can read about how to do this here .
2. In the table on the attached screenshot:
In the "Addresses" table, each address in the "Max" column has the same maximum number of uses.
In the "Used" column, using the COUNTIF formula, it is calculated how many times such an address is used in the "People" table (according to the "Address number" column (column L), but nothing prevents from completely refusing it and counting directly at the address itself (column K) ).
Column K "Address" is filled through drop-down lists, the data source for which are addresses from the table "Addresses" (Column A). This is done through validation. Plus in the convenience and inability to make a mistake when entering the address.
Column L "Address number" is filled in according to the address selected in the previous column by selecting the corresponding number from column B of the "Addresses" table. In principle, you can do without it, and count the use in column D not by address numbers, but by the addresses themselves.
Column M "Free for the selected address" is obtained in the same way by a selection from the "Addresses" table.