Auto Update cell value based on column value

[richramik1]

On the attached CALC sheet, I have a situation that I would like to automate, or at least make it simpler.

When a record is entered in the sheet, a TYPE is selected. FYI, the “P” suffix indicates that the bill is paid. No “P”, not paid. Therefore, on line 3, this is an original entry. No APPROVAL date, and no DAYS TO APPROVE value.

When the bill is approved, I am given an APPROVED DATE (col C). There is math in col D that subtracts the SUBMITTED from the APPROVED, to render the number of days it took to approve.

I then have to go back to col A and manually change the type, i.e., I go back to the drop down list and change the value to one that has the corresponding letter “P”. Line 3 then line 5.

I would truly like to automate this so that when there is a value in col D, it would automatically update the contents of col A to the corresponding “P” value as shown in the drop down list. See attached.

Thanks,

Rich Ramik
SAMPLE-P.xlsx (6.4 KB)

[fpy]

@richramik1

go back

typical XY problem - Wikipedia

what’s the use of the wannabe-dropdown ?
sounds like just an extra column …

@richramik1

math in col D

that’s the same logic to apply to your magical “P”
=IF(D5="", "", "P")

[richramik1]

The dropdown selects the “bucket” the associated money goes into.

The “A” signifies Administrative dollars and the “WX” signifies actual work dollars.

Furthermore, without the “P” indicates the Invoice is submitted, but not paid.

The “P” , i.e., as in AP or WXP indicates the invoice has been submitted and paid.

I run daily reports providing this data to our management team.

I’m sorry. I should have provided this information when originally submitted. That is the reason for the APPROVED date.

The concept is simple. If the value in Column E is other than NULL, then change the entry in Column A to a PAID value. Either add a “P” or change the contents to the proper P value: PSE&G-AP or PSE&G-WXP.