excel - Apply previous validation formula while copying - Stack Overflow

admin管理员组

文章数量:1194410

I have an Excel sheet with formulas applied in a column against other column.

The requirement I am trying to achieve right now is:

I want to copy the value from another file/column and paste it in this Excel while still applying validation formulas.

Example: Suppose my sheet has 6 rows and the validation formulas are:

1. In row 1, 2, 4, 5 and 6:

In column C, formula is INDEX(C:C, ROW()) > INDEX(A:A, ROW()) And in column E, formula is INDEX(E:E, ROW()) > INDEX(A:A, ROW()).

2. In row 3:

In column C, formula is AND(INDEX(C:C, ROW()) > INDEX(A:A, ROW()), INDEX(C:C, ROW()) > INDEX(B:B, ROW())) And in column E, formula is INDEX(E:E, ROW()) > INDEX(A:A, ROW()).

Desired behaviour:

1. If I copy data from another workbook into C1:C6, the validations should be triggered. Right now, the validations are being missed.

2. If I copy data from A1 into C1 (Suppose A1 had 5 and copying into C1, should actually fail since data validation rule ran)

3. If I frag data from C2 into C3, I want C3 validation to be triggered.

Right now, I do not see any of this happening.

Sample file: ;ouid=103822048169510741475&rtpof=true&sd=true

• When I copy A1:A6 into C1:C6, ideally all data validations should have turned wrong which is not the case.

Sample data:

| 1 |   | 2 |  | 3 |
| - | - | - |  | - |
| 2 |   | 3 |  | 4 |
| 3 | 5 | 4 |  | 5 |
| 4 |   | 5 |  | 6 |
| 5 |   | 6 |  | 7 |
| 6 |   | 7 |  | 8 |

本文标签： excelApply previous validation formula while copyingStack Overflow