Google Sheets – How to Categorize Cells

google sheets

I'm using Google Spreadsheets to manage my shared expenses with my girlfriend. It looks like this:

| Who   | What       | Category   | When         | Amount |
| Gui13 | Vegetables | Meals      | October 2012 | 26€    |
| GF    | AT&T       | Multimedia | October 2012 | 30€    |
...

Now, I'd like to categorize automatically the expenses, meaning autofill the "Category" column according to what's in the "What" column.

I have an extra sheet where I have 2 columns indicating which value should be categorized as this or that:

| What       | Category   |
| Vegetables | Meals      |
| Wine       | Meals      |
| AT&T       | Multimedia |
...

What I want to do is:

find if the "What" cell content is found in the 2nd sheet
if yes, fill the category with the content of the cell of the 2nd sheet
if no, nothing

I couldn't figure how to do that with GDocs. I think it would involve FILTER or INDEX, but I'm not sure. Any idea?

Example sheet: https://docs.google.com/spreadsheet/ccc?key=0AhTjRwDZNsNNdF90Ymx0clpCV0l1ckhscnBmRVRNU0E&usp=sharing

Best Answer

Try this:

=iferror(vlookup(C2,Sheet2!A$1:B$6,2,FALSE),"")

Here's an example.

Some notes:

Put the formula in the cell you want to fill
C2 refers to the value you're using to look up in the array
When entering the array range, make sure to put in $ before the row numbers, otherwise the range will be dynamic, not fixed.
If you add more categories, you'll need to update the formula on Sheet1

Related Solutions

How to Lock/Protect Cells in Google Sheets

It is not currently possible to lock a given cell. However, you can create new sheets in the spreadsheet and lock those. If you really want to prevent her from changing the formulas, you could have all of the results/formulas be in a second sheet, referring to the first sheet's data, then lock the second sheet so it would be read-only to her.

In order to reference data from one sheet in another, use the syntax sheetname!range. For example, to take the average of sheet1's column A in rows 2 through 10, you would use Average(sheet1!A2:A10).

To add sheets, use the + sign on the bottom right side of the spreadsheet. You can switch between and rename sheets using the tabs and dropdown arrows on the lower left side of the spreadsheet.

Google Sheets – How to Insert Copied Cells in Google Spreadsheets

Here's my current solution. It inserts a blank area the size of the selection, and shifts the relevant rows downward by one selection-height. The next step is to make it a full-fledged "insert copied cells" feature.

/* NOTE: THIS function does not preserve the semantics of formulas which point at the
   moved data.   
  In this way it is more disruptive than cut-and-paste, 
         which retargets references in existing formulas 
*/ 
function shiftCellsDown(){
  var SS = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SS.getActiveSheet();
  var selection = sheet.getActiveSelection();


  var selectedRow = selection.getRowIndex();
  var selectedCol = selection.getColumnIndex();
  var lastRow = sheet.getLastRow();

  var width = selection.getWidth();
  var height = selection.getHeight();

  var rangeToBeMoved =sheet.getRange(selectedRow, selectedCol, lastRow-selectedRow+height, width); 
  var dataToBeMoved = rangeToBeMoved.getValues();
  var row =  [];
  for ( var c =0; c< width; c++){
       row.push("");
  }
  for ( var r =0; r< height; r++){
       dataToBeMoved.splice(0,0,row);
  }

  sheet.getRange(selectedRow, selectedCol, dataToBeMoved.length,width).setValues(dataToBeMoved);
}

Best Answer

Related Solutions

How to Lock/Protect Cells in Google Sheets

Google Sheets – How to Insert Copied Cells in Google Spreadsheets

Related Topic