Forum Discussion

RimRock_Ranches's avatar
RimRock_Ranches
Copper Contributor
Jan 23, 2025

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.  

Link to data test sheet

 

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_Haman's avatar
    Rob_Haman
    Copper 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.

  • =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.

     

Resources