mykulpasskwa
Board Regular
- Joined
- Mar 20, 2018
- Messages
- 66
The title may not be on-the-nose, and before I get started the bottom line is I need Excel to indicate to me in the payments when a client reached a cap for each year across different years. If there's a better way than what I have below, I'm open to any creative ideas, this was just the best idea I've had.
I'm basically trying find the cells with the same year based on a term code, summing the payments next to those cells, and then highlight the payment where they reached the cap for that year. I know bits and pieces of how it should go, but I can't figure out the whole song.
I have a column (Lets say col A) with varying terms within a year, and each year has a different limits on what a client needs to pay. Once they reach that limit then the funding comes from a different source. Each term code is the four digit year and the single digit term, so this year would be 20201, 20202, 20203, etc.
The adjacent column (col B) has the payments, and they only need to pay up to a certain point but it's different every year. So in 2019 the cap was $350, but in 2020 the cap is $650. I'm trying to figure out how to group those term codes based on the year, then sum that group, and then format the cell where they reached the cap.
Attached is a thumbnail which I'd like the end product to look like that I manually put together. So in 2019 the cap was $350, but they didn't pay that until 20193 so that is what is highlighted in yellow. Comparatively, the cap in 2020 is $650, but they didn't reach cap until 20204 so that's what's highlighted. The main point being that both payments are highlighted because they are from different years and the user didn't have to intervene other than inputting the payments.
I've been struggling on this for about 6 months. Some days it seems like I'm over-complicating this and other days it feels like it's just not possible. Any help that can be provided is truly, truly appreciated and will actually allow me to complete the rest of this project. Please let me know if anything needs to be clarified or you need more information. Thank you!
I'm basically trying find the cells with the same year based on a term code, summing the payments next to those cells, and then highlight the payment where they reached the cap for that year. I know bits and pieces of how it should go, but I can't figure out the whole song.
I have a column (Lets say col A) with varying terms within a year, and each year has a different limits on what a client needs to pay. Once they reach that limit then the funding comes from a different source. Each term code is the four digit year and the single digit term, so this year would be 20201, 20202, 20203, etc.
The adjacent column (col B) has the payments, and they only need to pay up to a certain point but it's different every year. So in 2019 the cap was $350, but in 2020 the cap is $650. I'm trying to figure out how to group those term codes based on the year, then sum that group, and then format the cell where they reached the cap.
Attached is a thumbnail which I'd like the end product to look like that I manually put together. So in 2019 the cap was $350, but they didn't pay that until 20193 so that is what is highlighted in yellow. Comparatively, the cap in 2020 is $650, but they didn't reach cap until 20204 so that's what's highlighted. The main point being that both payments are highlighted because they are from different years and the user didn't have to intervene other than inputting the payments.
I've been struggling on this for about 6 months. Some days it seems like I'm over-complicating this and other days it feels like it's just not possible. Any help that can be provided is truly, truly appreciated and will actually allow me to complete the rest of this project. Please let me know if anything needs to be clarified or you need more information. Thank you!
