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()]"),"=","")
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()]"),"=","")- Detlef,
Thank you for your asssistance. The subbstitute fits perfectly. Can report, I have been employing this formula this am. It’s working. When finish my task I’ll report the full outcome next week.
Riny,
Thank you for your assistance and persistence. If not, for you no way we have Detlef chime in with his thoughts.
Guys...we teamed up well here:) Cooperation is a job well done. Awesome work!
Give update next week.
Scott
Scott1417 Sorry, you have lost me. Have no clue what you are trying to achieve. Perhaps someone else.