Google Sheets: Format cell background based on a value in a cell in another sheet
My problem: sometimes I forget to review published interviews for a talk.
I publish a weekly interview series, the "PostgreSQL Person of the Week" interviews. The data lives in a big spreadsheet, which has several sub-sheets.
The first one, the main sheet, is holding the overall status of all interviews, and has one column which indicates if the interview is complete.
Another sheet, the fifth one, holds the statistics data for my talk about what I learned interviewing the community.
The first column in the fifth sheet is copying the name from the first sheet. The second column indicates if I already looked over the interview and gathered all the statistics. And this is where today's problem came up: whenever I finished an interview (over 100 of them already) I marked this second column with a question mark ("?") to let myself know that at some point I need to read the interview again. But of course I don't always remember to do that.
My idea was to color the background of this cell, based on the value in the first sheet: make the cell background green when the interview is completed. This gives me an instant indication if I need to work on this interview or not.
Turns out this is a bit complicated, but possible.
Let's start with the cell background: the feature is named "Conditional formatting". Click on a cell, click the "Format" menu and select "Conditional formatting". Now you can enter rules for the current cell. I need this for multiple cells (almost the entire column), but more about this later.
In my case it is Cell B3, on the second sheet "Interview Stats".
The way to use other Sheets as criteria is to select "Custom formula is" in "Format rules". And the actual formula is:
=MATCH("X", (INDIRECT("'Speaker interviews'!"&ADDRESS(ROW(),8))), 0)
Let's depict the details here. Google Sheets can't refer directly to other Sheets, one needs to use INDIRECT() for this. The name of the other Sheet is the first paramener - and if the Sheet name contains whitespaces or special characters, it must be quoted with single quotes: 'Speaker interviews'.
This is then followed by an exclamation mark, and the cell which is referenced. In my case, the cell is composed as ADDRESS(), plus the &. The first parameter is the row (not the column), and here I'm using the ROW() function. This returns the row number in the current Sheet (3, from B3). The second parameter is the column number (not the column name) in the other Sheet (the column indicating that the interview is published). In my case it's column 8, or H. Since it's always H, I hardcode this number here. Otherwise there is also the COLUMN() function if one needs to reference the current column.
Therefore cell B3 in Sheet "Interview Stats" will reference to cell H3 in "Speaker interviews", B4 to H4, B5 to H5 and so on.
This address is then used by the MATCH() function. In my Sheets I'm using "X" everywhere a task is completed. The first and second parameter for MATCH() are the two values to compare - the "X" and the value of the field (hence the &) returned by ADDRESS(). The optional third parameter defaults to "1", which would MATCH() make compare this as a range. Setting this to "0" ensures that the exact value is compared.
If that formula returns true, the entire equation evaluates to true, and the color for the background is set. Green in my case.
Two things left to do: first, expand this to all columns:
Since I'm using the ROW() function, the conditional rule is automatically applied to all correct cells from B3 to B1000. And second: find all interviews which I published, but still have to revied for the talk.
Trackbacks
No Trackbacks
Comments
Display comments as Linear | Threaded