I have gotten the host cell to pull the very first value in the Q column when the C column has a checked box but as more boxes get checked, I'd like the host cell to stay up to date by displaying the next value in the Q column, and then the next, and then the next.

For reference, this is the formula I have in the display cell:

=INDEX(Q19:Q258,IF($C$19:$C$258=True,MATCH(Q19:Q258,Q19:Q258,0)))

I can tell that it's not quite right but everything I can think to do to try and manipulate it breaks one rule or another and only outputs an error.

If more context helps, I'm trying to create a student loan tracker for myself. The C column hold checkboxs that mark payments made and the Q column holds a formula that calculates and displays outstanding interest after a payment.

If this isn't possible, that's alright, but with my very very little knowledge of Google Sheets and some quick google searches I was able to get myself here, so I feel like it's reachable, I just don't have the knowledge. Appreciate any help!

I have gotten the host cell to pull the very first value in the Q column when the C column has a checked box but as more boxes get checked, I'd like the host cell to stay up to date by displaying the next value in the Q column, and then the next, and then the next.

For reference, this is the formula I have in the display cell:

=INDEX(Q19:Q258,IF($C$19:$C$258=True,MATCH(Q19:Q258,Q19:Q258,0)))

I can tell that it's not quite right but everything I can think to do to try and manipulate it breaks one rule or another and only outputs an error.

If more context helps, I'm trying to create a student loan tracker for myself. The C column hold checkboxs that mark payments made and the Q column holds a formula that calculates and displays outstanding interest after a payment.

If this isn't possible, that's alright, but with my very very little knowledge of Google Sheets and some quick google searches I was able to get myself here, so I feel like it's reachable, I just don't have the knowledge. Appreciate any help!

• google-sheets
• google-sheets-formula

Share Improve this question Follow edited Mar 8 at 0:51 Ken White 126k1515 gold badges236236 silver badges466466 bronze badges asked Mar 8 at 0:08 user29934757user29934757 311 silver badge11 bronze badge 2

• Would you be able to provide your sample sheet, with your initial output, and also your expected output so that we can further help you. You may use this to provide a markdown table (you may create one with the help of this link) – Jats PPG Commented Mar 8 at 0:57
• Make sure to provide input and expected output as plain text table in the question. Check my answer or other options to create a table easily, which are easy to copy/paste. Avoid sharing links like spreadsheets, which make the question useless for others or images, which are hard to copy. Also, note that your email address can also be accessed by the public, if you share Google files. – TheMaster Commented Mar 8 at 5:46

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

This formula will return the last value from column Q where column C is true.

=CHOOSEROWS(FILTER(Q19:Q, C19:C), -1)

You want the next (unchecked) value in Column Q.

Try this formula:

=indirect(address(19+(countif(C19:C256,true)),17,4,true))

• countif: returns the number of checked checkboxes in column C
• address: this is the "address" of the next unchecked value in Column Q
• indirect: return the value in the address.