Forum Discussion
RimRock_Ranches
Jan 23, 2025Copper Contributor
Help on a Formula for checkbook register
I am using excel as a checkbook register. I print my own checks but sometimes hand write a check on the same account so the numbers are not in series. I have a formula to auto increment my check number column only if a reference cell is "Printed Check", and I type in the hand written check numbers. I'm looking for help modifying my formula to ignore the hand written check numbers and find the last (or largest) printed check number. The formula currently is: =IF(B11="Printed Check",(XLOOKUP(TRUE,ISNUMBER($A$1:A10),$A$1:A10,,,-1)+1),"-") All of my hand written checks will be below 15000.
I linked a test sheet above but data looks like:
15100 Printed Check
15101 Printed Check
15102 Printed Check
- E-mail Transfer
- E-mail Transfer
- Auto Pay
- Direct Deposit
- Auto Pay
15103 Printed Check
701 Hand Check
702 Printed Check
703 Printed Check
For now I can use this formula and brute force it by typing the next printed check number after a hand written check but it would be nice to have the formula do that for me. I've tried using < 15000 logic modifiers but have not been successful yet. I'm only YouTube certified in excel but this has been a fun project so far. I do have a 30 day trial of Copilot but wasn't able to find a solution that way either. I hope somebody is bored on a lunch break or something and willing to help, thanks! 😁
=IF(B2="Printed Check",(XLOOKUP(1,ISNUMBER($A$1:A1)*($B$1:B1="Printed Check"),$A$1:A1,,,-1)+1),"-")
How about this formula in cell A2? Cell A1 has the start value 15100. Then fill the formula down from A2.
As variant, slightly modified - in A1
=IF( B1= "Printed Check", 15099+COUNTIFS($B$1:$B1, B1), "-" )
and drag it down
- Rob_HamanCopper Contributor
Recommend to use the formula as =MAXIFS($A$1:A999,$B$1:B999,"Printed Check")+1, this will always give you the latest number +1.
- OliverScheurichGold Contributor
=IF(B2="Printed Check",(XLOOKUP(1,ISNUMBER($A$1:A1)*($B$1:B1="Printed Check"),$A$1:A1,,,-1)+1),"-")
How about this formula in cell A2? Cell A1 has the start value 15100. Then fill the formula down from A2.
- RimRock_RanchesCopper Contributor
That works great, thank you!