Spencer

Using sets of IMPORTRANGE() formulas to hide the “live” formulas. This may be done with 2 or 3 documents – 2 is simpler, 3 is more secure. Source: https://docs.google.com/spreadsheets/d/1WDjDm0EZV76Pxwx3qisOHuOYuP3Ctdlm4wxHoOl8pWs/edit#gid=0 Formulas: https://docs.google.com/spreadsheets/d/15bxDakBx9rgRiws-NMTgmyGiKK-iYmm_YOkH2Ed5yjQ/edit#gid=0 Middle: https://docs.google.com/spreadsheets/d/1i_Jaua74U7J6PVM5J-Th68CN6iObrfww0MEFshIosoU/edit#gid=0 Connect with me: • spencer.farris@gmail.com • spencerfarris.me • www.linkedin.com/in/spencer-farris/ • Twitter @FarrisSpencer read more

How to setup 2-way sync within the same workbook without any unique ID. Important notes: * This is the least efficient of the 3 methods, so I would suggest against it unless absolutely necessary Sheet (Make a copy from the File menu) https://docs.google.com/spreadsheets/d/1Pp1OvXNJjCK7nyBMS2g_Czg0-XjLXRNKVKWUlUZ4qLc Script: function read more

Setting up 2-way sync within the same spreadsheet where the ID and the row DO NOT have a mathematical relation. Important notes: * Remember to increment the /row/ variable after the loop * This assumes the FILTER()/QUERY()/IMPORTRANGE() etc. formula is already setup and functioning properly. read more

How to write a custom function and return the sheet names. Sheet (to copy from the File menu): https://docs.google.com/spreadsheets/d/19ismgyyfalmi3WedEH1fhs-_Zt8jP4v3QD9HRDUIDdo/edit Scripts: /** * Returns the name of the current tab. * @return Current tab name. * @customfunction */ function sheetname(){ return SpreadsheetApp.getActive().getActiveSheet().getName(); } /** * Returns read more

How to move all checked rows to another tab. Important notes: * Keep your math correct when iterating through the data array, deleting items from the Sheet, and deleting items from the array Sheet (to copy from File then “Make a copy”) https://docs.google.com/spreadsheets/d/1o-zBFV9Mqi5hngWhB9jK4xQNo4sMxrvWBemroUBMy5o/edit#gid=0 Script: function read more

How to create multi-select dropdowns in Google Sheets Important notes: * Don’t run the function manually, it will fail. * Make sure the row and column in the IF() statement match the cell/range you want to include the dropdowns * The red triangle at the read more