autofill check boxes and have their link cell update

[stuck]

If you put a check box in a cell and link the check box to that cell then ticking/unticking the check box toggles the cell value TRUE/FALSE so far, so simple.

When you use the autofill handle on the cell and drag it down then the checkbox also autofills, again so far so simple.

My problem is that the link cell on the check box boxes created by autofill still points to the original linked cell, even if you make the reference to the linked cell relative not absolute, e.g. make the linked cell $C$1 and drag down and all the new boxes all still point to $C$1 BUT if I make the linked cell $C1 (or C1) and drag down the new boxes still point to $C1, when I expected them to point to $C2, $C3, etc. (or C2, C3 etc.).

It seems I'm not the only person to be stumped by this:
https://social.technet.microsoft.com/Fo ... orum=excel
I've tried the solution suggested on that page and while it does work it's not ideal. Yes, selecting a cell is enough to populate that cell but to clear that cell you must select another cell and then reselect the original one. If a user was working their way down a list to include/exclude items in the list and they accidentally select the wrong item then the need to select away and then reselect to restore things is clumsy.

So... if there isn't a way to propagate a check box such that the linked cell ref updates relative and not absolute then is there another way of simulating a check box?

Ken

[Rudi]

Hi,

See if this article from Debra assists...
(Just change the lCol variable to 0 since your linked cell is the same cell that contains the checkbox).

[stuck]

Ah! That looks very promising.



Ken