Excel question

[rowyn]

My father has some checklists that he tracks in Excel. Parts, but not all, of the different lists overlap. He wants to be able to set up the lists so that if he deletes an item from a specific section of one list, it'll delete the item from all lists. Conversely, if he adds an item to a list, he wants it to be added automatically to all lists. Obviously, he can set up big sections of each sub-list to point to a big section of the main list, and just have a lot of blank lines on each list. But a more elegant solution would be nice, because the lists area already very long and he'd rather keep it tidy.

Anybody know of a way to do this? I know that if I have charts that read data from cells A1-C4, and I add a row between lines 2 and 3, or delete line 3, Excel will automatically update my chart, correctly incorporating or removing the date. So it's not a wholly alien concept to Excel. But I don't know if there's a way to adapt this to make an attractive checklist.

Edit: My father worked for 25 years as a programmer at IBM, and is much more technically savvy than I am, as a general rule. So there's no need to be concerned that any existing solution would be too complicated for him to implement. :D

Comments

[level-head.livejournal.com]

It sounds, offhand, like something that could be structured a little differently:

One sheet with everything on it, and a column for each checklist. You could color the cells to indicate whether the item is needed on that list, then mark with a character to indicate that you've got it/done it/whatever.

That way, adding an item/row will allow you to move across and indicate which checklists it's part of. And deleting a row kills it all the way across.

Different display-only layers could be built for individual lists. In fact, a single display sheet is all you need: identify the column, and have it pick out just those items appropriate along with their status. But the updating would be on the master layer.

A bit of a script could be added (the only thing a VB script would be needed for) that adds buttons to the master layer, if you like, to hide items on the active checklist (where your cursor is) that do not apply. Move to another cell, click the button again, and it would unhide all rows and again hide the ones without the "need this" color in the same column your cursor is in.

This would get rid of clutter (blank rows). But without it, the system would work fine just with formulae and no programming at all.

===|==============/ Level Head