+ Reply to Thread
Results 1 to 9 of 9

Formula that starts where previous row left off

  1. #1
    Registered User
    Join Date
    12-05-2012
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2010
    Posts
    5

    Formula that starts where previous row left off

    I have a nested if statment that goes down a row until a certain value is found. Once that value is found, it pastes the value in a different sheet. The problem is that if the formula gets to row 71 before it finds a "match", it will paste the proper value, but when it continues, then next row's formula begins at row70, so it will find row 71 and also find a "match", so I will end up with the same line.

    Is there a way to start a formula based on the result of the formula in the previous row? For example, if I started searching for a value on row 1 of sheet A and didn't find a match until row 10 of sheet A (result pastes in row 1 of Sheet B), the formula in row 2 of sheet B would begin with evaluating with row 11 of sheet A?


    =IF('Allocated Spending Plan - Dec'!I69="Savings",'Allocated Spending Plan - Dec'!B69,IF('Allocated Spending Plan - Dec'!G69="Savings",'Allocated Spending Plan - Dec'!B69,IF('Allocated Spending Plan - Dec'!I70="Savings",'Allocated Spending Plan - Dec'!B70,IF('Allocated Spending Plan - Dec'!G70="Savings",'Allocated Spending Plan - Dec'!B70,IF('Allocated Spending Plan - Dec'!I71="Savings",'Allocated Spending Plan - Dec'!B71,IF('Allocated Spending Plan - Dec'!G71="Savings",'Allocated Spending Plan - Dec'!B71,IF('Allocated Spending Plan - Dec'!I72="Savings",'Allocated Spending Plan - Dec'!B72,IF('Allocated Spending Plan - Dec'!G72="Savings",'Allocated Spending Plan - Dec'!B72,IF('Allocated Spending Plan - Dec'!I73="Savings",'Allocated Spending Plan - Dec'!B73,IF('Allocated Spending Plan - Dec'!G73="Savings",'Allocated Spending Plan - Dec'!B73,IF('Allocated Spending Plan - Dec'!I74="Savings",'Allocated Spending Plan - Dec'!B74,IF('Allocated Spending Plan - Dec'!G74="Savings",'Allocated Spending Plan - Dec'!B74,"")
    Last edited by jwortsman; 12-06-2012 at 10:32 AM. Reason: Closed

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    12,847

    Re: Formula that starts where previous row left off

    Okay, here's what I did (your formula is scary )
    I created a dummy column to track when Savings appears in G or I. I put it in N. This can be hidden later if you like It looks like this
    =IF(OR(I69= "Savings", G69= "Savings"), "S_" & COUNTIF($N$68:N68,"S_*"),"")
    This results in a sequence as follows S_0, S_1, S_2 and so on.

    Then in the other sheet where you want your results (in my example, it's in Sheet2 starting in A2)
    =IFERROR(INDEX('Allocated Spending Plan - Dec'!$B$69:$B$73,MATCH("S_"&ROW(A1)-1,'Allocated Spending Plan - Dec'!$N$69:$N$73,0)),"") (modify ranges as needed.
    Questions?
    Attached Files Attached Files
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    12-05-2012
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Formula that starts where previous row left off

    You're very right, my formula is VERY scary. Didn't know a better way to do it. I like what you've done and I think it's going to work. A little more transparency. I'm trying to create a master register for a budget where ALL transactions in all accounts are entered. These formulas would then split the transactions into individual registers for the specific accounts. Sample attached.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    12-05-2012
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Formula that starts where previous row left off

    Thanks for the help. I've attached a spreadsheet what I'm trying to do. This may offer some clarification.

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    12,847

    Re: Formula that starts where previous row left off

    Okay, I fixed your formulas in the dummy columns. For example, in in Y2, you had this

    =IF(OR(J2= "Wells Checking", H2= "Wells Checking"), "S_" & COUNTIF($V1:W$68,"S_*"),"")
    1. It should be referencing F2 and H2
    2. The COUNTIF portion is counting how many matches you've already had for that catagory (thus in that column) and will attach that to "S_"
    so should be COUNTIF($Y$1:Y1, "S_*"). As you drag that down, it'll change (i.e. in Y10, it would be COUNTIF($Y$1:Y9,"S_*") )

    So the formula in Y2 should be
    =IF(OR(F2= "Wells Checking", H2= "Wells Checking"), "S_" & COUNTIF($Y$1:Y1,"S_*"),"")

    I am not sure how you are using these values though. You'd need to reference the appropriate column when using Index. For example, for Wells Saving, it would be column AA.
    If you could clarify a little how these numbers are going into sheet2, I might be able to help you more.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    12-05-2012
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Formula that starts where previous row left off

    Really appreciate the help. I have set-up sheet 2 (at least the skeleton, no formulas) and the idea is that Sheet 1 will contain every transaction regardless of account. For example, Row 3 is a transfer from Citi Checking to Wells Checking. On sheet 2, this transaction should appear under each account (Citi Checking and Wells Checking - the date, amount and description). Row 14 on Sheet 1 should appear as an entry on Sheet 2 under Citi Checking and Citi Savings (again, date, amount and description). The way I was using the formula you provided was that if that line was identified (columns U,W,Y,AA) then I could key off of that identifier to pick up the data for sheet 2.
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    12,847

    Re: Formula that starts where previous row left off

    Okay, gotta go for the day but I set up the formulas in A:D on Sheet2. They are similar to the ones we already discussed. See if you can do the next set. I also wasn't sure where you wanted "Description" taken from. I used & to combine Category and Subcategory in Col Q on the first sheet.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    12-05-2012
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Formula that starts where previous row left off

    This is awesome! I went in and updated the other cell ranges and added description and everything seems to work as intended! Thank you very much for your help!

  9. #9
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    12,847

    Re: Formula that starts where previous row left off

    Glad it's all working for you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1