Singing Potatoes
Previous — Main Page — Up to Archive — Next
Wednesday, 10 March 2004
New Territory

I've been programming computers for a quarter of a century, and have never used a spreadsheet. Until now.

I'm surprised there doesn't seem to be a for loop available for use in formulas. I'm trying to put together a spreadsheet with a series of "checkboxes" in each row. I want the rightmost cell to look at each of the checkboxes, and if they contain an "X", then look up a value in the corresponding cell in a hidden row, and add that value to the line total. Right now, I've got a huge formula in each line total cell which goes:

=IF(CELL("CONTENTS";C3)="X";$C$2;0)+
IF(CELL("CONTENTS";D3)="X";$D$2;0)+
IF(CELL("CONTENTS";E3)="X";$E$2;0)+[...]

...and continues in this vein all the way through column O. There's gotta be an easier way to do it, but I haven't found it yet. Also, I'm still trying to find a way to count all the filled checkboxes in a particular column, but that's still eluding me. I can't do a hardcoded formula this time, because the number of rows will change. (I suppose I could use "1" instead of "X", and just do a SUM on the column, but dagnab it, I want to use "X"!)

Posted by godfrey (link)
Previous — Main Page — Up to Archive — Next
Comments
It cyber sucks! (He's doing it all for me, folks.)
Posted by Ginevra on 7 Jan 2004 14:30