RAJESH1960
Banned for repeated rules violations
- Joined
- Mar 26, 2020
- Messages
- 2,313
- Office Version
- 2019
- Platform
- Windows
Hello guys,
I need a little help in altering and adding 2 more conditions in the formula. The formula has 2 corrections to be made. One is instead of giving the result as “Not in GST”, I want the same date derived from the formula as in “Tally” in place of “Not in GST”, as mentioned in the formula. The second one it is displaying a mistake when the amount in Tally is displayed above GST in all the rows.
I am trying to get the dates as per GST in all the rows and same date for the same invoice numbers in Tally.
I need a little help in altering and adding 2 more conditions in the formula. The formula has 2 corrections to be made. One is instead of giving the result as “Not in GST”, I want the same date derived from the formula as in “Tally” in place of “Not in GST”, as mentioned in the formula. The second one it is displaying a mistake when the amount in Tally is displayed above GST in all the rows.
I am trying to get the dates as per GST in all the rows and same date for the same invoice numbers in Tally.
query.xlsx | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | |||
1 | A | B | C | D | No. | Get Dates | Date | Amount | As Per | Result | |||
2 | 1 | 301 | NOT IN GST | 27-03-2021 | 7350.00 | TALLY | Correct | ||||||
3 | 2 | 117 | 06-07-2020 | 06-07-2020 | 22050.00 | GST | Correct | ||||||
4 | 3 | 117 | 06-07-2020 | 08-07-2020 | 22050.00 | TALLY | Correct | ||||||
5 | 4 | 127 | 09-07-2020 | 09-07-2020 | 22050.00 | GST | Correct | ||||||
6 | 5 | 127 | 09-07-2020 | 11-07-2020 | 22050.00 | TALLY | Correct | ||||||
7 | 39 | 551 | NOT IN GST | 19-09-2020 | 26775.00 | TALLY | If Not in GST then dislpay tally date 19-0-2020 | ||||||
8 | 40 | 594 | 19-09-2020 | 05-01-2021 | 2688.00 | TALLY | Mistake | Has to show 08-01-2021 | |||||
9 | 41 | 594 | 08-01-2021 | 08-01-2021 | 2688.00 | GST | Correct | ||||||
10 | 109 | 1209 | 12-03-2020 | 12-03-2020 | 5264.00 | GST | Correct | ||||||
11 | 148 | 694 | 31-03-2021 | 31-03-2021 | 472.00 | GST | Correct | ||||||
12 | 149 | 437 | 28-02-2021 | 28-02-2021 | 2006.00 | GST | Correct | ||||||
13 | 150 | 150 | 17-03-2021 | 17-03-2021 | 6510.00 | GST | Correct | ||||||
14 | 207 | 263 | NOT IN GST | 22-05-2020 | 2822.00 | TALLY | If Not in GST then dislpay tally date 19-0-2020 | ||||||
15 | 208 | 758 | 02-07-2020 | 02-07-2020 | 1050.00 | GST | Correct | ||||||
16 | 209 | 32 | 02-07-2020 | 02-07-2020 | 1050.00 | TALLY | Correct | ||||||
17 | 210 | 32 | 26-10-2020 | 26-10-2020 | 1050.00 | GST | Correct | ||||||
18 | 211 | 758 | 26-10-2020 | 26-10-2020 | 1050.00 | TALLY | Correct | ||||||
19 | 212 | 2001 | 19-06-2019 | 19-06-2019 | 1956.00 | GST | Correct | ||||||
20 | 213 | 1979 | 11-06-2019 | 11-06-2019 | 2003.00 | GST | Correct | ||||||
21 | 214 | 2490 | 17-01-2020 | 17-01-2020 | 2100.00 | GST | Correct | ||||||
22 | 4010 | 111 | 17-01-2020 | 01-05-2021 | 2100.00 | TALLY | Mistake | Has to show 09-05-2021 | |||||
23 | 4011 | 111 | 09-05-2021 | 09-05-2021 | 2100.00 | GST | Correct | ||||||
24 | 4012 | 111 | NOT IN GST | 09-05-2021 | 2101.00 | TALLY | Correct | ||||||
Exact Match |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F2:F24 | F2 | =IF(I2="GST",G2,IF(COUNTIFS(I:I,"GST",E:E,E2,H:H,H2),G1,"NOT IN GST")) |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
F2:F24 | Expression | =COUNTIFS($E$2:$E$1264,F2)>1 | text | NO |
Last edited: