Add Letter To Existing Text in a Cell - PART 2

[richramik1]

Hey Gang:

I apologize as I should have gone into more detail in my previous post.

The following has to do with tracking invoicing: date submitted; date approved; number of days to approve. I will use the actual cell addresses taken from my spreadsheet.

F3 – INVOICE SUBMITTED
G3 – INVOICE APPROVED

The formula below calculates the number of days it took to approved the invoice. The date submitted and approved are entered manually.

=IF(G3="",0,IF(G3=F3,1,IF(G3>F3,DAYS(G3,F3))))

In another cell is the TYPE CODE. This indicates the particular vendor and type of work that was done. Initially, this is entered from a drop down list, i.e., LMN-WX or XYZ-WX.

When the APPROVED DATE is entered, the calculation above is executed. My goal is to automatically change the TYPE CODE to PAID by appending the letter P to the particular vendor, i.e., LMN-WXP or XYZ-WXP when the APPROVED DATE is entered.

In turn this executes a SUMIF which calculates by LMN and/or XYZ.

Therefore, CONCAT or CONCATENATE will not meet the requirement. Neither will manually searching/changing. I want to utilize the capabilities provided/derived within CALC.

Thanks In Advance,
Rich Ramik

[Villeroy]

Accounting on sheets is the most valid reason to get fired (unless you are the almighty boss, of course).

[gregors15]

Have a look at the attached file, without seeing your file, I have added column C, and used formulas to calculate columns C, E, and L, used your formula for column H. Let us know if it helps.

LO20210913.ods (22.8 KB)

[eeigor]

@gregors15

I have added column C…

Overall, not bad. Only I would replace the &P status with the value “paid” in a separate column like yours.
But changing the code in the cell, as the questioner asks, is an irrational decision, and it is necessary, if possible, to revise it.
The data in the field must be atomic, or the data in the field must be indivisible. No need to add a new feature to the code. But if you really want to (save on a column), then everything is possible. But this requires a macro. And event driven code. And Calc adds crashes and freezes.

However, you can remove column C and set the conditional formatting of the rows with the past payment. Payment details already exist.

@richramik1

My goal is to automatically change the TYPE CODE to PAID by appending the letter P to the particular vendor

Is this a mandatory requirement?

Edit: The ideal solution is below.

[LeroyG]

Working on @gregors15 sample file, I added conditional formatting to column B. So in B3:B20 Formula is H3>0, and Apply Style: is set to Paid (a new cell text style with format code @"P").

Can’t remove column C, until fixing formula in column L.

See sample file (23.8 KB).

Tested with LibreOffice 7.0.6.2 (x64) on Windows 10.0.
.

[eeigor]

@LeroyG

a new cell text style with format code @"P"

@LeroyG: Great! Add some color to it: [MAGENTA]@“P”
Note: I don’t understand how to enter normal quotes: they are replaced automatically.

@LeroyG

Can’t remove column C, until fixing formula in column L.

Remove the “C” column:
=SUMIFS(E$3:E$20;H$3:H$20;">0";B$3:B$20;K5&"*")

Or so: The paid ones turn black. Format code: [BLACK]@“P”

LO20210913 (1).ods (25.3 KB)

[LeroyG]

@eeigor

I don’t understand how to enter normal quotes

Double quotation marks (") have been replaced.

And if they are replaced, press inmediatelly Ctrl+Z.

[richramik1]

I have attached a sample of my actual sheet. There are notes to the right for further information.
TESTING XXXXXXXXXXXX - Copy - Copy.xls (74 KB)

[gregors15]

OK, now I see your file, the only way I can see to do want you want is to have another column(F) “Type Status”, you might want to protect it, so it couldn’t be over written and use a formula to update it when paid. I have added another column to the Type codes sheet to enable the index/match formula to work to update the new column(F).
LO20210913A.xls (75.5 KB)
. You would need to change your summary to run of the new column(F). Have a look at the new file based on yours, let us know what you think.

[richramik1]

I see what has been done. Collectively, what you folks have done is an interesting approach. I never would have thought of using INDEX. However, I believe I will use CONCATENATE as part of my equation in what will now be the new column I, i.e., “Days to Aprv”.

In a previous life, I worked in Bell Labs: late 60s - early 80s. If what I was trying to do didn’t exist, I would make a call and get it fixed or have it created. I knew the boys in New Providence (Murray Hill). From my perspective, I would seem rather counter intuitive that such an operation/function of adding a letter(s) to an existing cell doesn’t exist. Maybe something for consideration in a future release.

At any rate, I want to thank you for your assistance.

Stay Safe,
Rich Ramik