If only there is paste special [17 paste special tricks] for everything in life, things would be much more smoother and fun. Alas, the paste special is limited only to excel.
Here is a handy trick you can use to quickly scale up data validations set up in one cell to say a range of cells.
Just copy the cell by pressing CTRL+C and then go to the target cell(s) and press ALT+ESN. And your data validations are pasted in the new cells. That is all. Quick and Easy.
Using this, it is very easy, for eg. to copy the data validation you have setup to accept only unique values in cells from one work sheet to another.
Browse more quick tips.
One Response to “How to compare two Excel sheets using VLOOKUP? [FREE Template]”
Maybe I missed it, but this method doesn't include data from James that isn't contained in Sara's data.
I added a new sheet, and named the ranges for Sara and James.
Maybe something like:
B2: =SORT(UNIQUE(VSTACK(SaraCust, JamesCust)))
C2: =XLOOKUP(B2#,SaraCust,SaraPaid,"Missing")
D2: =XLOOKUP(B2#,JamesCust, JamesPaid,"Missing")
E2: =IF(ISERROR(C2#+D2#),"Missing",IF(C2#=D2#,"Yes","No"))
Then we can still do similar conditional formatting. But this will pull in data missing from Sara's sheet as well.