# Formula that starts where previous row left off

1. ## 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,"")

2. ## 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?

3. ## 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.

4. ## 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. ## 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.

6. ## 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.

7. ## 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.

8. ## 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. ## Re: Formula that starts where previous row left off

Glad it's all working for you.

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

#### 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