MY PREFERENCE IS TO HAVE A MACRO BUTTON [EITHER FORMULA OR VBA] AND HAVE ALL THESE TASKS EMBEDDED IN IT BASED ON THESE CONDITIONS.
[SAMPLE OF SPREADSHEET]
MANUF No SUB No. DESCRIPTION ** UNIT QUANTITY
A1234 94456 TYPE A, XXXX XXX * * * * EA ** * * * 45
A4235 20374 YA1AM, XXXXXXXXXX * **EA ** * * * 55
A1482 31375 TYPE B, XXXXXXXXX * * * EA ** * * * 33
A1461 51725 A1AAM, XXXXXXXXXX * * EA ** * * * 67
A1356 21873 TYPE A, XXXXXXXXX * * * EA ** * * * 24
1. CONDITION #1:
IF "DESCRIPTION" TAB ["C" COLUMN] CONTAINS "TYPE A" AS PART OF TEXT/DESCRIPTION, TAKE THE "QUANTITY" TAB ["E" COLUMN] SPECIFIED ON THAT PARTICULAR ROW AND DIVIDE IT BY 20 & REPLACE PREVIOUS QTY.
EXAMPLE: IF C2 CELL [TYPE A] WAS CALCULATED:
• THE QUANTITY IN THAT ROW, E2 [39] WILL BE DIVIDED BY 20
• AND ROUNDED UP TO THE NEAREST ONE, [39/20=1.95 ROUNDUP TO NEAREST ONE=2]
• AUTOMATICALLY CHANGE THE ORIGINAL QUANTITY IN E2 BY THE ABOVE ROUNDUP TO “2”.
• NEED A ONE SHOT DEAL TO AUTOMATICALLY CALCULATE THE CELLS BY CLICKING ON A MACRO BUTTON/FORMULA/CODE [?]
2. CONDITION #2:
IF "DESCRIPTION" ["C" COLUMN] CONTAINS "TYPE B" AS PART OF TEXT/DESCRIPTION, TAKE THE "QUANTITY" SPECIFIED ON THAT PARTICULAR ROW ["E" COLUMN] AND DIVIDE IT BY 10, ROUNDED UP TO NEAREST ONE.
3. AUTOMATICALLY SAVE THE EXCEL FILE AFTER THIS MACRO HAS BEEN UTILIZED.
Bookmarks