Formula for subtracting from a total, if a specific item is selected from a dropdown

[angdilla]

Hello! Is there a way to automatically subtract 1 from a cell when an item is selected from a dropdown? I have a specific number of times I can use each item in the dropdown menu and would like to keep track of how many remaining times are available for each item.

Thank you!

 

[mumps]

You would need a list of the items in the drop down with the corresponding number of times for each item. For example, this can be done by listing the items in column A and their corresponding times in column B all on a different sheet. You could then keep track in column C how many times each item is used. What are the items in the drop down and what are their corresponding numbers? In which cell is the drop down found? We could save a lot of time if you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[angdilla]

Hello! Is there a way to automatically subtract 1 from a cell when an item is selected from a dropdown? I have a specific number of times I can use each item in the dropdown menu and would like to keep track of how many remaining times are available for each item.

Thank you!

You would need a list of the items in the drop down with the corresponding number of times for each item. For example, this can be done by listing the items in column A and their corresponding times in column B all on a different sheet. You could then keep track in column C how many times each item is used. What are the items in the drop down and what are their corresponding numbers? In which cell is the drop down found? We could save a lot of time if you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

Thank you!

FUNDS AVAILABLE SPREADSHEET.xlsx

Shared with Dropbox

www.dropbox.com

On the Grants tab, columns I-W (row 7 and lower) have a drop-down with 35 different codes. The corresponding numbers tracked are on the Funds Available tab in columns G and O (they are also copied on to rows 2-4 of the Grants tab).

My goal is to enter a grant on the Grants tab, select the different funds I am pulling from in columns I-W, and have 1 removed for the remaining points each time I use a code.

 

[mumps]

In column A of the Funds Available sheet, you have two occurrences of 'WMALDOC", one for Education and one for Health. When you select "WMALDOC " in the drop downs in the Grants sheet, how do you determine which of the two occurrences of 'WMALDOC" to update in Funds Available sheet?

 

[angdilla]

In column A of the Funds Available sheet, you have two occurrences of 'WMALDOC", one for Education and one for Health. When you select "WMALDOC " in the drop downs in the Grants sheet, how do you determine which of the two occurrences of 'WMALDOC" to update in Funds Available sheet?

That's a great question. If manually doing it, we would just choose the best option. If it is possible to have a formula like I am hoping, we could have both WMALDOC-E and WMALDOC-H as dropdown options to eliminate the duplicate fund code.

 

[mumps]

Click here to download your file. I have had to remove trailing spaces in some of the Fund names in columns A and I of the Funds Available sheet. I have also added WMALDOC-E and WMALDOC-H to the Fund Codes sheet to update the drop downs and also in column A of the Funds Available sheet. Simply make a selection in the drop downs in the Grants sheet. The macro is in the code module for the Grants sheet. To view the code, right click the tab name for your Grants sheet and click 'View Code'. Close the code window to return to your sheet. This is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    Dim lRowG As Long, lRowFA As Long, fund As Range
    lRowG = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    If Intersect(Target, Range("I7:W" & lRowG)) Is Nothing Then Exit Sub
    lRowFA = Sheets("Funds Available").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Set fund = Sheets("Funds Available").Range("A2:A" & lRowFA, "I2:I" & lRowFA).Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
    fund.Offset(, 5).Copy
    If Not fund Is Nothing Then
        fund.Offset(, 6).Value = fund.Offset(, 6).Value - 1
    End If
    Application.ScreenUpdating = False
End Sub

 

[angdilla]

Click here to download your file. I have had to remove trailing spaces in some of the Fund names in columns A and I of the Funds Available sheet. I have also added WMALDOC-E and WMALDOC-H to the Fund Codes sheet to update the drop downs and also in column A of the Funds Available sheet. Simply make a selection in the drop downs in the Grants sheet. The macro is in the code module for the Grants sheet. To view the code, right click the tab name for your Grants sheet and click 'View Code'. Close the code window to return to your sheet. This is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    Dim lRowG As Long, lRowFA As Long, fund As Range
    lRowG = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    If Intersect(Target, Range("I7:W" & lRowG)) Is Nothing Then Exit Sub
    lRowFA = Sheets("Funds Available").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Set fund = Sheets("Funds Available").Range("A2:A" & lRowFA, "I2:I" & lRowFA).Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
    fund.Offset(, 5).Copy
    If Not fund Is Nothing Then
        fund.Offset(, 6).Value = fund.Offset(, 6).Value - 1
    End If
    Application.ScreenUpdating = False
End Sub

I apologize if it's a user error, but this doesn't seem to be working. When I select a code from the dropdown on the Grants tab, it doesn't update the total number of available points for that code.

 

[mumps]

You will notice that I selected WENDENC in cells I7 and K8. You will also notice that the value in cell F2 has decreased by 2 from the original value of 120.

angdilla.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2	WACDTAC	1	WBUREMC	2	WENDENC	118	WFORDPC	3	WHANBJC	3	WLUTSTC	8	WMCGMCC	2	WPERBBC
3	WAXLDEC	2	WCASFAC	4	WFELNAC	4	WGABMOC	3	WKERFAC	3	WMALDOC EDUCATION	3	WMORDAC	2	WPETCAC
4	WBLOBEC	2	WDOUEAC	2	WFISGAC	2	WGEHTRC	2	WLISBTC	8	WMALDOC HEALTH	3	WOBRSMC	3	WPETMAC
5					UNRESTRICTED	HEALTH	EDUCATION	FUNDS AVAILABLE
6	Award Date	Grantee	Project	Total Grant	57,000.00	800.00	800.00	58,600.00	Funds Used	Funds Used	Funds Used	Funds Used	Funds Used	Funds Used	Funds Used
7								Yes	WENDENC
8								Yes			WENDENC
Grants

angdilla.xlsm
	A	B		D	E	F	G	H
1	Fund	Purpose		Points Starting Balance	Spring Points Used	Fall Points Used	Points Remaining
2	WACDTAC	Unrestricted		1			1
3	WAXLDEC	Unrestricted		2			2
4	WBLOBEC	Unrestricted		2			2
5	WBUREMC	Unrestricted		2			2
6	WCASFAC	Unrestricted		4			4
7	WDOUEAC	Unrestricted		2			2
8	WENDENC	Unrestricted		120			118
9	WFELNAC	Unrestricted		4			4
10	WFISGAC	Unrestricted		2			2
11	WFORDPC	Unrestricted		3			3
Funds Available

The macro is working properly for me unless I misunderstood your request.

 

[angdilla]

You will notice that I selected WENDENC in cells I7 and K8. You will also notice that the value in cell F2 has decreased by 2 from the original value of 120.

angdilla.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2	WACDTAC	1	WBUREMC	2	WENDENC	118	WFORDPC	3	WHANBJC	3	WLUTSTC	8	WMCGMCC	2	WPERBBC
3	WAXLDEC	2	WCASFAC	4	WFELNAC	4	WGABMOC	3	WKERFAC	3	WMALDOC EDUCATION	3	WMORDAC	2	WPETCAC
4	WBLOBEC	2	WDOUEAC	2	WFISGAC	2	WGEHTRC	2	WLISBTC	8	WMALDOC HEALTH	3	WOBRSMC	3	WPETMAC
5					UNRESTRICTED	HEALTH	EDUCATION	FUNDS AVAILABLE
6	Award Date	Grantee	Project	Total Grant	57,000.00	800.00	800.00	58,600.00	Funds Used	Funds Used	Funds Used	Funds Used	Funds Used	Funds Used	Funds Used
7								Yes	WENDENC
8								Yes			WENDENC
Grants

angdilla.xlsm
	A	B		D	E	F	G	H
1	Fund	Purpose		Points Starting Balance	Spring Points Used	Fall Points Used	Points Remaining
2	WACDTAC	Unrestricted		1			1
3	WAXLDEC	Unrestricted		2			2
4	WBLOBEC	Unrestricted		2			2
5	WBUREMC	Unrestricted		2			2
6	WCASFAC	Unrestricted		4			4
7	WDOUEAC	Unrestricted		2			2
8	WENDENC	Unrestricted		120			118
9	WFELNAC	Unrestricted		4			4
10	WFISGAC	Unrestricted		2			2
11	WFORDPC	Unrestricted		3			3
Funds Available

The macro is working properly for me unless I misunderstood your request.

My mistake! The macro was being blocked. Thank you so much for your work on this!

 

[mumps]

You are very welcome.