Ordering An Unordered World

I don't really use my smartphone very often. It's nice to have and comes in handy sometimes, but I prefer being mostly "disconnected". And when I am connected, I prefer to be sitting down at a desktop computer.

However, since we all got smartphones it provided an opportunity to bring some order to the family chaos and clutter. I setup a family Google Calendar and a Google Sheets Shopping List that seem to be working well.


Family Calendar

Family Shopping List

Family Shopping List - items sorted by location in store

https://docs.google.com/spreadsheets/d/1TlpSiT9hIyNnFfYaOr1sKy9Fpi_ojV1lH5Cpgv3g0lI/edit?usp=sharing

 

You have to be signed in to your google account to make a copy (gets saved to your google drive)

 

Sheet Descriptions

Items – list of possible items and their aisle\location in our grocery store (this mapping could be more normalized so there’s an aisle\location associated with each store, but currently it refers to our main grocery store)

Order – maps aisle\location to shopping order (the path we usually take through the store). I took pictures of the store aisles and uploaded them to Google Drive for reference.

Stores – list of stores

List – shopping list

Sorted – shopping list sorted by shopping order

Freezer – list of items in the freezer

Pantry – list of items in the pantry

Dinners – list of dinner options

 

Adding a new item

Add new row to Items sheet and then should be available in Items, Freezer, and Pantry sheets

 

Under the hood

List sheet – Column A: right click->data validation->criteria->list from range-> Items!A2:A282  // causes items from Items sheet to be shown in dropdown list

List sheet – Column B: =LOOKUP(A2,Items!A$2:A$572,Items!B$2:B$572) // finds the aisle\location for the item by looking it up in Items sheet

List sheet – Column C: right click->data validation->criteria->list from range-> Stores!A2:A7  // causes stores from Stores sheet to be shown in dropdown list

List sheet – Column D: =LOOKUP(B2,Order!A$2:A$50,Order!B$2:B$50) // finds the shopping order for the item by looking it up in the Order sheet using the aisle\location of the item

Sorted sheet – Column A: =SORT(List!A2:D1021,List!C2:C1021,TRUE,List!D2:D1021,TRUE,List!A2:A1021,TRUE) // displays items from List sheet sorted by Store, ShoppingOrder, Item

 

Other

Uses large 24 pt font so easily visible on phone

Google Sheets phone app doesn’t support drop down lists  - can add items manually and then fix on computer

To mark item off on phone: select cell->font toolbar item->strike through  (we don’t usually bother)

To propagate cell formula (e.g. Store or ShoppingOrder): select cell and then drag bottom right corner of cell to cells below