Aug 30 2021 04:20 PM
Have an excel file with a column that has an equation. Essentially, I want to see the newest payment (from the equation) in another column.
Want to do the following in a column next to the previous one:
1. Convert a formula to text.
2. If only one number (maximum length ###,###.##) add that text after the "=" sign.
3. Every time add a number to the equation, populate the nth string to this secondary column.
4. Keep repeating until the last nth string is placed into the equation.
Attachment shows what trying to achieve.
Thank you for your help.
Scott
Aug 30 2021 10:19 PM
@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.
Aug 31 2021 03:42 PM
@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?
Aug 31 2021 10:54 PM
@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)
Sep 02 2021 10:51 AM
@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?
Sep 02 2021 11:24 AM
@Scott1417 Sorry, you have lost me. Have no clue what you are trying to achieve. Perhaps someone else.
Sep 02 2021 11:44 AM
SolutionYou 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()]"),"=","")
Sep 03 2021 10:50 AM
Sep 02 2021 11:44 AM
SolutionYou 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()]"),"=","")