I am building a spreadsheet for tracking bill payments. I have an entry sheet where the user enters the name, amount, frequency, # of payments and start date of the bill (ex. Mortgage, $2000, monthly, 60 [payments], Oct 1, 2023). I have a master sheet that then uses a series of array formulas to determine the payment dates for each of the payments based on the info in the entry sheet. It performs this task for multiple bills and stacks them all into the one master sheet. That part is working great!
On a separate sheet, I have a filter that shows a table of the payments filtered for a specific time frame (say all the payments coming due in Feb 2024) and a calendar that uses the filtered data to display the due dates in calendar format. Again, all good there.
What I would like is to be able to use checkboxes on the second sheet to check off from the filter list when a bill has been paid and have it be updated on the master sheet of bill data. I obviously cannot alter the data presented in the filter, but I was thinking that perhaps I could "switch it" somehow? Almost like a toggle?
Any help would be greatly appreciated.
P.S. I am trying to do this without a script, if possible.
I tried simply adding a column of checkboxes beside the filtered data and conditionally formatting them so they could only be seen when there was data in the adjacent rows, but that does not "stick" to the master data and does not clear when the filter criteria is changed thus not meeting the objective of "tracking" anything.
I also tried using a unique ID approach for both the master payment data and the check/not_checked data and correlating the two, but that plan flopped!