Update cell based on several IF criteria

[rz6657]

I'm trying to automate a column for the users where a "Type" is updated based on the criterion below... This would really save them a lot of time as they deal with over 200 rows per file and they update the type per each manually - this is daily as its changes frequently... please refer to the attachment

Criteria:
IF columns E or F have a total greater than $0, enter MDE in the corresponding row in column D
IF column D already has MDE in the cell because columns E or F calculated 1st to update it, and any column from G - N has a total less than or greater than $0, leave as MDE
IF column D has no MDE because columns E or F equals to $0, than update as CR if row 4 has a CR number
IF column D has no MDE because columns E or F equals to $0, than update as Other if row 4 has an Other
If any of the columns have $0 and row 4 is empty (in this case J - N, do nothing


Note: columns E or F need to be precede the calculation (be calculated 1st) for column D before the other 4...

[LeoTaxi]

Could there be more then 1 column other ?

Cheers
Leo

[rz6657]

You know what, Yes... there's a possibility of another "Other"... good catch. The CRs will always remain unique...

[LeoTaxi]

Dont have solution with formula then

maybe with VBA, a button to update ?


Kind regards
Leo

[rz6657]

I can do some handy formula, so are you suggesting I post in the VBA forum to see if they have a way to do it or that you may have a VBA solution?

[leelnich]

Assuming you just want a formula for column D (paste in D5 and copy down):

Please Login or Register  to view this content.

This finds the last non-zero number in the row and returns "MDE", "CR", or the corresponding row 4 text value ("Other" or "").

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

[leelnich]

...Or are you reading the amount and other info elsewhere and trying to get the correct column to update? If so, where is that info?

[rz6657]

The formula you provided is great; however, i failed to explain 2 items...

1. Once the MDE is populated and than I enter a CR # on row 4, it does populate CR in column D and leaves the MDE as is
2. However, once I add Other on row 4, it will override the already populated CR in column D
3. I forgot that I have an additional column O... that is DDE... it will follow the same criteria where if already column D is already populated by MDE or CR or Other, only populate the rows that column D is empty with DDE...
4. We follow this pattern: MDE, CR#s, Other and finally DDE
5. At the end, all of the column D will be populated with MDE, CR, Other or MDE where there is a MOTS ID / Application Acr populate in columns B and C....

Thanks so much for working on this...

[leelnich]

Try this (in D5 and down):

Please Login or Register  to view this content.

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

[LeoTaxi]

Solution with the update button

Please Login or Register  to view this content.

Kind regards
Leo