Google Sheets – Complex VLOOKUP, Checkboxes, and Pivots

google sheetsgoogle-sheets-arrayformula

I have a situation where room reservations can be managed by checkboxes. You can see an example in the screenshot below. I've consolidated several sheets into one to provide visualization but the data lives in separate sheets in the actual document…

 1. Columns A-L are a "Manage Reservations" sheet.
 2. Columns N-O are a "Pricing" sheet.
 3. Columns Q-R would be the output on a "Reservation
    Calendar" sheet.

I have two questions:

1. Is there a better way to calculate revenue in column L?

The method I'm using is working but it's a beast to manage, doesn't feel efficient and I'm sure there's some swankier way to do a lookup and SUM of the checkboxes / room number -> room rate.

2. How can I output the data in columns B-K (that represents the reservations) into what is shown in column Q-R?

I can't seem to figure out a way to QUERY or PIVOT the checkboxes and dates they represent into individual rows in another sheet. I had first tried some PIVOT tables but was running into issues with, what I gather is the source data formatting.

I'd like to keep the formatting of columns A-L, if possible. It's easy to manage and visualize. That said, if it needs to change to optimize the calculations and allow for me to get the output of columns O-P on another sheet… I'll take that as a lesson in better spreadsheet architecting.

Sample Reference Data Sheet

Any help would be appreciated!

Best Answer

You want an efficient alternative to calculate revenue in column L.

Try this formula in the Cell L3 (based on the sample data below:

=arrayformula(sumproduct(C3:K3,pricing!$D$2:$L$2))

Sumproduct multiples, then sums, the values in multiple arrays. Note that the pricing data has been transposed so that the data is taken row-wise.

Unfortunately, SUMPRODUCT can't be used in with ARRAYFORMULA, so you have to copy the formula down as many rows as required.

You want an alternative formula for displaying the data in Columns Q-R.

Try this formula: in cell N2 (based on the sample data below:

=query({"Date","Room","";ARRAYFORMULA({IFERROR(SPLIT(FLATTEN(IF(B3:B="",,B3:B&"♦"&C2:E2&"♦"&C3:E)), "♦"))})},"select Col1, Col2 format Col1 'dd-MMM-yyyy'")

This formula automatically populates the output for each row

Sample data and results

Short Answer

Use arrays in Google Sheets and SORT() function.

Example

To combine the non continuous ranges A1:D5, H1:K100, P1:S15, the following formula will return a 4 X 120 array sorted by the first column in ascending order.

=SORT({A1:D5;H1:K100;P1:S15},1,TRUE)

The first parameter also could be any function that returns an array like IMPORTFEED.

Remarks

The URLs provided in example are not working because the YouTube Data API (v2) has been officially deprecated as of March 4, 2014.
IMPORTJSON() was retired of the new Google Sheets.

References

Google Sheets – Can I Protect the Formatting?

The way to address the problem is to use a combination of custom function in google spreadsheet and google script. I will use examples to try to address solution for your problem

Formula protection :

My understanding is you will have users enter new entries into a new row. This means you could apply the formula automatically to this new row, which might prevent them from modifying that formula in the first place

1) To apply the formula to the new row, use ArrayFormula. The below example will sum values from column F to Column I in each row

=arrayFormula((F:F+G:G+H:H+I:I))
 read as = F1+G1+H1+I1 in row 1, =F2+G2+H2+I2 in row2 and so on

you can get more elaborate and remove 0 values from the sheet like so

=arrayFormula(if((F:F+G:G+H:H+I:I) = 0,"",(F:F+G:G+H:H+I:I)))

More details can be found here. By doing so you negate the need for a user to type out the formula and hence lesser modification. ArrayFormula automatically applies/fixes the formula even when a new row is inserted or deleted.

2) Since the arrayFormula would be applied in the first row (or a specific row). Set Protection in the first row(or that specific row) so that only you can modify it and which indirectly means no one can modify any formula in the sheet. This doesn't prevent user for modifying below row 1 which affects how arrayFormula works, that is why you have use column formula protection (described Below)

Format Protection:

1) Similar to above, you can set up the first row to be the master row which will be used to format the remaining rows. Since you will also lock it down, no one except you will be able to modify it!

2) To automatically copy the formatting, you can use onEdit(e) trigger in google sheets. The below code does exactly that:

function onOpen(e){
 var ui = SpreadsheetApp.getUi()
 ui.createMenu("Format sheet").addItem("Format", "setFormat").addToUi()

}
function onEdit(e){           //This is activated each time a modification happens in the sheet
  var ss = SpreadsheetApp.getActive()
  var sheet =ss.getActiveSheet()
  if(sheet.getSheetName() == "Sheet1"){
  var entryRange = e.range
  var range  = sheet.getRange(1,entryRange.getColumn(),1,entryRange.getNumColumns())              //This will be you range to get the formatting from row "1" and corresponding column based on the column being edited
  Logger.log(entryRange.getA1Notation())
  range.copyFormatToRange(sheet, entryRange.getColumn(), entryRange.getNumColumns()+entryRange.getColumn()-1, entryRange.getRow(), entryRange.getNumRows()+entryRange.getRow()-1)
  Logger.log(entryRange.getColumn())
  if(entryRange.getColumn() == 10){                                 //This column value will not be allowed modified except for row 1 of that column
    if (entryRange.getRow() != 1){                                  //The columns in row "1" will be allowed to modified
    e.range.setValue((e.oldvalue == undefined? "": e.oldvalue))
    }
  }
  }
}

function setFormat(){
  var ss = SpreadsheetApp.getActive()
  var sheet = ss.getActiveSheet()
  var firstRow = sheet.getRange(1, 1, 1, sheet.getLastColumn())
  var dataRange = sheet.getDataRange()
  firstRow.copyFormatToRange(sheet, 1, dataRange.getNumColumns(), 2, dataRange.getNumRows())

}

Copy the above to your script Editor in google sheets and save it. Then reload your google sheet. At this point, anything you modify in row "1" will be unaffected. However, any changes to rows below that will trigger onEdit to automatically copy the format from the first row and apply it to that row in the corresponding column.

Column Formula Protection: This code within the onEdit function will prevent modification to specific column, column 10 in this case. But can be modified to protect any column. (To understand why we need this, read Formula Protection: 2) above)

if(entryRange.getColumn() == 10){                                 //This column value will not be allowed modified except for row 1 of that column
        if (entryRange.getRow() != 1){                                  //The columns in row "1" will be allowed to modified
        e.range.setValue((e.oldvalue == undefined? "": e.oldvalue))
        }
      }

It will replace the oldvalue back in column 10 from column 2 onwards. However, if someone copies a whole new row in, which also happens to modify row 10, this will not prevent it from getting modified.

You can modify it to allow you access to modify row 10 like so

if(entryRange.getColumn() == 10 && Session.getEffectiveUser().getEmail() != "Your Email Here")

Note: If you make this modification, make sure to run the onEdit function from script editor once to give it permission to access your emailID.

Finally, When you reload the sheet you will notice a new menu item called Format Sheet, you can use this to apply the format in row "1" to all the rows below it! Which is done by the function setFormat(). This a fallback code, in case people modify the formatting after onEdit trigger sets the formatting. onEdit(e) is not triggered when the user modifies the format of a cell.

Hope that helps!