Forum Discussion
Excel Formula nth String
You are working backwards.
Everyone else puts the numbers in column B and then puts the formula in C3:
=SUM(C$3:C3)Maybe that is what confuses Riny_van_Eekelen.
Try this:
=--SUBSTITUTE(FILTERXML("<y><z>"&SUBSTITUTE(FORMULATEXT(B3),"+","</z><z>")&"</z></y>","//z[last()]"),"=","")
Scott1417 Not sure if what you describe is really what you want. The example you gave suggests you want to see a running total in the table. and then the grand total and the latest billing amount at the top.
If so, we can take it from there. See attached.
Riny_van_Eekelen First Thank you for your response. Very much appreciate your efforts and expertise.
Secondly, definitely a misunderstanding. Re-attached the .xlxs.
Column B has a simple addition formula in each row record. This is what I am trying to achieve...if a new monthly bill comes in, then place that new monthly bill in column C.
Some clients will have no payment. Others like Client 12 will have 12 monthly bills. In this case, I want to see the last monthly bill that closes-out the account.
Does this make more sense now?
Scott1417 Not really! I see all you are trying to achieve and still believe that column B merely holds a running total of the amounts in column C. What you call "a simple addition formula" is what I could call a major abuse of Excel. Is there any reason, why you want to 'hard-code' all the preceding values from column C and the current one in column B.
In B2, you could use either of the two following formulae (and copy them down) to achieve the same result:
=SUM($C$2:C2) or =SUM(B2,C1)Riny_van_Eekelen No. New approach in the .xlsx attachment. Column B has the equation (formula) and want Column C to automatically show the last entry from Column B.
For example,
B4 is =933.93+1396.76
C4 should automate as a value is added. So it will show $1,396.76.
The Test Function column is close but no cigar. Not even sure it is on the right path.
Ideas?
You are working backwards.
Everyone else puts the numbers in column B and then puts the formula in C3:
=SUM(C$3:C3)Maybe that is what confuses Riny_van_Eekelen.
Try this:
=--SUBSTITUTE(FILTERXML("<y><z>"&SUBSTITUTE(FORMULATEXT(B3),"+","</z><z>")&"</z></y>","//z[last()]"),"=","")