hello
I need a favor to resolve some of my issues to automate my sheet:
- is it possible to add a drop-down date selection for cells A and N and R (the reason I need this: is to reduce typing)
- if cell B contains the word KOHE or SPWL or HUBC then it should automatically change the cell B color and 7.50% should automatically come in cell H (the reason I need this: to identify the different rates)
- similarly, if cell B contains the word PABC then it should automatically change the cell B color and 25.00% should automatically come in cell H (the reason I need this: to identify the different rates)
- if cell E is <> 0 then the color for the cell E to K should change to yellow but once there is a value in cell N then the same cells must change to green (the reason I need this: if there is no item then I have the blank cells. but once there are expectations of receiving some results in the future then temporarily change to yellow as a reminder and once it is received then change to green)
- in rows 38 39 40 41, i want to get rid of unnecessary 0s - these should be blank and clean and only particular row should change and have value once i have some to add in that row
== i am trying to add a mini sheet here but please note that total range for the sheet is 2 to 1200 (so formulas, conditions etc will be applied like 2:1200
thank you
I need a favor to resolve some of my issues to automate my sheet:
- is it possible to add a drop-down date selection for cells A and N and R (the reason I need this: is to reduce typing)
- if cell B contains the word KOHE or SPWL or HUBC then it should automatically change the cell B color and 7.50% should automatically come in cell H (the reason I need this: to identify the different rates)
- similarly, if cell B contains the word PABC then it should automatically change the cell B color and 25.00% should automatically come in cell H (the reason I need this: to identify the different rates)
- if cell E is <> 0 then the color for the cell E to K should change to yellow but once there is a value in cell N then the same cells must change to green (the reason I need this: if there is no item then I have the blank cells. but once there are expectations of receiving some results in the future then temporarily change to yellow as a reminder and once it is received then change to green)
- in rows 38 39 40 41, i want to get rid of unnecessary 0s - these should be blank and clean and only particular row should change and have value once i have some to add in that row
== i am trying to add a mini sheet here but please note that total range for the sheet is 2 to 1200 (so formulas, conditions etc will be applied like 2:1200
thank you
| Payout Sheet.xlsx | ||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | |||
| 1 | Ex-Date | Script | Shares | Face Value | Cash Dividend % | Cash Dividend Receivable | Cash Dividend Receivable Per Share | Tax Deductions % | Tax Deductions Amount | Cash Dividend Tax Charged Per Share | Cash Dividend Received Per Share | Cash Dividend Received | Dividend Warrant Received | Date Dividend Cash Received | Bonus Shares % | Bonus Shares | Bonus Shares Confirmation Received | Date Bonus Shares Received | Right Shares % | Right Shares | Right Shares Premium | Right Shares Face Value With Premium | Right Shares Confirmation Received | Date Right Shares Received | ||
| 2 | 15-Apr-2021 | PKGS | 100 | 10 | 225.00% | 2,250.00 | 22.50 | 15.00% | 338.00 | 3.38 | 19.12 | 1,912.00 | Yes | 25-May-2021 | 0.00% | 0 | ||||||||||
| 3 | 16-Sep-2021 | KOHE | 1,000 | 10 | 52.50% | 5,250.00 | 5.25 | 7.50% | 394.00 | 0.39 | 4.86 | 4,856.00 | Yes | 04-Oct-2021 | 0.00% | 0 | ||||||||||
| 4 | 06-Oct-2021 | NATF | 1,000 | 5 | 100.00% | 5,000.00 | 5.00 | 15.00% | 750.00 | 0.75 | 4.25 | 4,250.00 | Yes | 03-Nov-2021 | 25.00% | 250 | Yes | 05-Nov-2021 | ||||||||
| 5 | 10-Nov-2021 | SPWL | 5,000 | 10 | 20.00% | 10,000.00 | 2.00 | 7.50% | 750.00 | 0.15 | 1.85 | 9,250.00 | Yes | 25-Nov-2021 | 0.00% | 0 | ||||||||||
| 6 | 10-Nov-2021 | GHGL | 2,000 | 10 | 15.00% | 3,000.00 | 1.50 | 15.00% | 450.00 | 0.23 | 1.28 | 2,550.00 | Yes | 24-Nov-2021 | 0.00% | 0 | ||||||||||
| 7 | 10-Dec-2021 | KOHE | 1,000 | 10 | 77.50% | 7,750.00 | 7.75 | 7.50% | 581.25 | 0.58 | 7.17 | 7,168.75 | Yes | 23-Dec-2021 | 0.00% | 0 | ||||||||||
| 8 | 31-Jan-2022 | SPWL | 5,000 | 10 | 50.00% | 25,000.00 | 5.00 | 7.50% | 1,875.00 | 0.38 | 4.63 | 23,125.00 | Yes | 21-Feb-2022 | 0.00% | 0 | ||||||||||
| 9 | 07-Feb-2022 | KAPCO | 3,500 | 10 | 40.00% | 14,000.00 | 4.00 | 15.00% | 2,100.00 | 0.60 | 3.40 | 11,900.00 | Yes | 24-Feb-2022 | 0.00% | 0 | ||||||||||
| 10 | 10-Feb-2022 | APL | 500 | 10 | 150.00% | 7,500.00 | 15.00 | 15.00% | 1,125.00 | 2.25 | 12.75 | 6,375.00 | Yes | 01-Mar-2022 | 0.00% | 0 | ||||||||||
| 11 | 24-Feb-2022 | KOHE | 1,000 | 10 | 27.50% | 2,750.00 | 2.75 | 7.50% | 206.25 | 0.21 | 2.54 | 2,543.75 | Yes | 08-Mar-2022 | 0.00% | 0 | ||||||||||
| 12 | 28-Feb-2022 | SUTM | 200 | 10 | 50.00% | 1,000.00 | 5.00 | 15.00% | 150.00 | 0.75 | 4.25 | 850.00 | Yes | 15-Mar-2022 | 0.00% | 0 | ||||||||||
| 13 | 18-Apr-2022 | PAKOXY | 520 | 10 | 0.00% | 0.00 | 0.00 | 15.00% | 0.00 | 0.00 | 0.00 | 0.00 | 25.00% | 130 | Yes | 19-May-2022 | ||||||||||
| 14 | 13-Apr-2022 | SPWL | 5,000 | 10 | 10.00% | 5,000.00 | 1.00 | 7.50% | 375.00 | 0.08 | 0.93 | 4,625.00 | Yes | 18-May-2022 | 0.00% | 0 | ||||||||||
| 15 | 20-Apr-2022 | SGF | 1,000 | 10 | 20.00% | 2,000.00 | 2.00 | 15.00% | 300.00 | 0.30 | 1.70 | 1,700.00 | Yes | 18-May-2022 | 0.00% | 0 | ||||||||||
| 16 | 27-Apr-2022 | ENGRO | 500 | 10 | 120.00% | 6,000.00 | 12.00 | 15.00% | 900.00 | 1.80 | 10.20 | 5,100.00 | Yes | 17-May-2022 | 0.00% | 0 | ||||||||||
| 17 | 13-May-2022 | PABC | 1,000 | 10 | 15.00% | 1,500.00 | 1.50 | 25.00% | 375.00 | 0.38 | 1.13 | 1,125.00 | Yes | 26-May-2022 | 0.00% | 0 | ||||||||||
| 18 | 21-Jul-2022 | SPWL | 5,000 | 10 | 45.00% | 22,500.00 | 4.50 | 7.50% | 1,687.00 | 0.34 | 4.16 | 20,813.00 | Yes | 05-Aug-2022 | 0.00% | 0 | ||||||||||
| 19 | 11-Aug-2022 | KOHE | 1,000 | 10 | 15.00% | 1,500.00 | 1.50 | 7.50% | 113.00 | 0.11 | 1.39 | 1,387.00 | Yes | 19-Aug-2022 | 0.00% | 0 | ||||||||||
| 20 | 18-Aug-2022 | WAVES | 7,500 | 10 | 0.00% | 0.00 | 0.00 | 15.00% | 0.00 | 0.00 | 0.00 | 0.00 | 20.00% | 1,500 | Yes | 05-Dec-2022 | ||||||||||
| 21 | 20-Sep-2022 | CEPB | 2,150 | 10 | 0.00% | 0.00 | 0.00 | 15.00% | 0.00 | 0.00 | 0.00 | 0.00 | 10.00% | 215 | Yes | 21-Oct-2022 | ||||||||||
| 22 | 11-Oct-2022 | HUBC | 30 | 10 | 155.00% | 465.00 | 15.50 | 7.50% | 34.88 | 1.16 | 14.34 | 430.12 | Yes | 01-Nov-2022 | 0.00% | 0 | ||||||||||
| 23 | 12-Oct-2022 | NATF | 8,250 | 5 | 100.00% | 41,250.00 | 5.00 | 15.00% | 6,187.50 | 0.75 | 4.25 | 35,062.50 | Yes | 01-Nov-2022 | 0.00% | 0 | ||||||||||
| 24 | 28-Oct-2022 | ENGRO | 500 | 10 | 100.00% | 5,000.00 | 10.00 | 15.00% | 750.00 | 1.50 | 8.50 | 4,250.00 | Yes | 16-Nov-2022 | 0.00% | 0 | ||||||||||
| 25 | 04-Nov-2022 | SPWL | 5,000 | 10 | 15.00% | 7,500.00 | 1.50 | 7.50% | 563.00 | 0.11 | 1.39 | 6,937.00 | Yes | 21-Nov-2022 | 0.00% | 0 | ||||||||||
| 26 | 09-Nov-2022 | FABL | 1,000 | 10 | 55.00% | 5,500.00 | 5.50 | 15.00% | 825.00 | 0.83 | 4.68 | 4,675.00 | Yes | 23-Nov-2022 | 0.00% | 0 | ||||||||||
| 27 | 02-Dec-2022 | GGL | 11,000 | 10 | 0.00% | 0.00 | 0.00 | 15.00% | 0.00 | 0.00 | 0.00 | 0.00 | 10.00% | 1,100 | Yes | 16-Dec-2022 | ||||||||||
| 28 | 23-Feb-2023 | KOHE | 1,000 | 10 | 30.00% | 3,000.00 | 3.00 | 7.50% | 225.00 | 0.23 | 2.78 | 2,775.00 | Yes | 06-Mar-2023 | 0.00% | 0 | ||||||||||
| 29 | 07-Mar-2023 | KAPCO | 3,500 | 10 | 35.00% | 12,250.00 | 3.50 | 15.00% | 1,838.00 | 0.53 | 2.97 | 10,412.00 | Yes | 21-Mar-2023 | 0.00% | 0 | ||||||||||
| 30 | 20-Apr-2023 | PAKOXY | 1,000 | 10 | 0.00% | 0.00 | 0.00 | 15.00% | 0.00 | 0.00 | 0.00 | 0.00 | 25.00% | 250 | Yes | 20-May-2023 | ||||||||||
| 31 | 20-Apr-2023 | SPWL | 5,000 | 10 | 25.00% | 12,500.00 | 2.50 | 7.50% | 938.00 | 0.19 | 2.31 | 11,562.00 | Yes | 05-May-2023 | 0.00% | 0 | ||||||||||
| 32 | 03-May-2023 | KOHE | 1,000 | 10 | 20.00% | 2,000.00 | 2.00 | 7.50% | 150.00 | 0.15 | 1.85 | 1,850.00 | Yes | 11-May-2023 | 0.00% | 0 | ||||||||||
| 33 | 28-Sep-2023 | APL | 2,000 | 10 | 150.00% | 30,000.00 | 15.00 | 15.00% | 4,500.00 | 2.25 | 12.75 | 25,500.00 | No | 0.00% | 0 | |||||||||||
| 34 | 28-Sep-2023 | POL | 2,000 | 10 | 600.00% | 120,000.00 | 60.00 | 15.00% | 18,000.00 | 9.00 | 51.00 | 102,000.00 | No | 0.00% | 0 | |||||||||||
| 35 | 06-Oct-2023 | HUBC | 500 | 10 | 60.00% | 3,000.00 | 6.00 | 7.50% | 225.00 | 0.45 | 5.55 | 2,775.00 | No | 0.00% | 0 | |||||||||||
| 36 | 18-Oct-2023 | AIRLINK | 3,000 | 10 | 25.00% | 7,500.00 | 2.50 | 15.00% | 1,125.00 | 0.38 | 2.13 | 6,375.00 | No | 0.00% | 0 | |||||||||||
| 37 | 19-Oct-2023 | TGL | 5,000 | 10 | 60.00% | 30,000.00 | 6.00 | 15.00% | 4,500.00 | 0.90 | 5.10 | 25,500.00 | No | 0.00% | 0 | |||||||||||
| 38 | 0.00% | 0.00 | 0.00 | 15.00% | 0.00 | 0.00 | 0.00 | 0.00 | 0.00% | 0 | ||||||||||||||||
| 39 | 0.00% | 0.00 | 0.00 | 15.00% | 0.00 | 0.00 | 0.00 | 0.00 | 0.00% | 0 | ||||||||||||||||
| 40 | 0.00% | 0.00 | 0.00 | 15.00% | 0.00 | 0.00 | 0.00 | 0.00 | 0.00% | 0 | ||||||||||||||||
| 41 | 0.00% | 0.00 | 0.00 | 15.00% | 0.00 | 0.00 | 0.00 | 0.00 | 0.00% | 0 | ||||||||||||||||
PSX Payouts Status | ||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F2:F41 | F2 | =(C2*E2)*D2 |
| G2:G41 | G2 | =IF(C2>0,F2/C2,0) |
| J2:J41 | J2 | =G2-K2 |
| K2:K41 | K2 | =IF(C2>0,L2/C2,0) |
| L23:L41,L2:L21 | L2 | =F2-I2 |
| I32:I41,I30,I26:I28,I20:I24,I4:I17 | I4 | =F4*H4 |
| P2:P41 | P2 | =C2*O2 |
| T2:T41 | T2 | =IF(AND(C2<>"",S2<>""),C2*S2,"") |
| V2:V41 | V2 | =IF(AND(D2<>"",U2<>""),D2+U2,"") |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| M1001:M1131,Q1001:Q1131,M2:M168,Q2:Q168,W2:W168 | Cell Value | contains "No" | text | NO |
| M1001:M1131,Q1001:Q1131,M2:M168,Q2:Q168,W2:W168 | Cell Value | contains "Yes" | text | NO |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| B2:B41 | List | =#REF! |
