In fact, if you use indirect cell references, you can make the whole thing sortable, and you can delete cells without getting referencing errors. It is a bit tricky setting it up the first time, but you can then just paste your data in and leave the formulas unchanged. This makes it much more general-purpose, and it automatically updates as you delete cells. In the above example, you could use the following formulas in row 2, for example Column C =concatenate("B",row()) Column D =concatenate("B",row()-1) Column E =if(indirect(c2)=indirect(d2),1,0) indirect(c2) will be translated to a reference to cell B2, and indirect(d2) will refer to cell b1 you then copy these formulas into all following rows, of course. You can now delete just cells A2 and B2, moving other cells up, and everything gets recalculated.
|