SOLVED

Sentence from excel data

Copper Contributor

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

Dagni470_0-1658832405705.png

 

Please can someone share formula for it :) 

3 Replies
best response confirmed by Dag470 (Copper Contributor)
Solution

@Dag470 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.

 

@Dag470 

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 :)
1 best response

Accepted Solutions
best response confirmed by Dag470 (Copper Contributor)
Solution

@Dag470 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.

 

View solution in original post