But It Worked Just A Moment Ago!

As so often happens in technology when one sits down to create the final product, the platform or software exhibits new and frustrating behavior that did not appear anywhere in any beta or test run.

A while ago, I created a mockup of a Google form for a babysitting co-op that my wife and I are starting with some local families. For more on the idea, check out my post on Dadding Ideas. This test form and its associated spreadsheet worked like a charm. I know enough about spreadsheets to get myself really dug in deep with no solution. This time, however, I fully used the sumif function and came up with a pretty good sheet that summed all the childcare given and received for each family and adjusted their bank accordingly.

I entered data in the sheet by filling in the form a few times. Little did I know that this was the crucial step that led to radically different behavior between this test and what was to eventually be the real form. After the data was entered, I then created the cells that tracked individual family balances. These cells were higher on the sheet than the last data entered from the form. As new data was entered, all my columns updated the way they were supposed to, and I felt great.

In the more elaborate form which allowed families to enter hours and minutes (in 15 minute increments) and included family names instead of placeholder family1, family2, etc, I created the tracking cells right away. I made my more elaborate sumif statements, combined the hours and minutes, calculated totals, and then split the minutes back into hours and minutes. I was psyched until I started entering data in the form to test it out.

The new data inserted rows in the middle of my carefully prepared spreadsheet and wrecked havoc on all of the calculations. I searched help and user threads for an answer. I could not figure out why seemingly on this spreadsheet data created a new row while on my test sheet, the data seemed to just lay down in the next cells below previous data without disturbing the rest of the sheet. That was really not the case, but it took me a while to figure that out.

Finally, I entered enough bogus data to pass the 14 rows of family info. I then repositioned the data that had been pushed down 14 rows back to where it should be, deleted the data from the spreadsheet. New data appears below row 14 and does not disrupt my calculations. This is a very inelegant solution, but it will work.

This is just one of those cases that I know enough to create a Frankenstein solution that will look good to those who don’t work with spreadsheets for their living. Anyone who does, however, will roll their eyes and know exactly how to create an elegant solution for my spreadsheet woes.

---