Sentence from excel data

New Contributor

How i can make sentence in 9B update automatically as when cost changes in C column(Column D is formula too)? 



Please can someone share formula for it  

3 Replies
best response confirmed by Dagni470 (New Contributor)

@Dagni470 I made a start in the attached example and suggest that you finish it yourself. Note that I used a helper column that generates the (£0.5k) type notation. You can do that in B9 as well but then the formula becomes much longer and more difficult to maintain.



In B9:

=B3&" "&TEXT(ABS(C3),"£0.0,k")&" "&D3&" / "&B4&" "&TEXT(ABS(C4),"£0.0,k")&" "&D4&" / "&B5&" "&TEXT(ABS(C5),"£0.0,k")&" "&D5&" / "&B6&" "&TEXT(ABS(C6),"£0.0,k")&" "&D6&" / "&B7&" "&TEXT(ABS(C7),"£0.0,k")&" "&D7


or if you have Microsoft 365, Office 2019 or Office 2021:


=TEXTJOIN(" / ",TRUE,B3:B7&" "&TEXT(ABS(C3:C7),"£0.0,k")&" "&D3:D7)

Thank you, works perfect