conditional formattingformulasgoogle sheets
I have a spreadsheet keeping track of the various documents that students of mine have been turning in. The first column contains names and each column after tracks a particular document.
Is there a way for a student's name to be highlighted if any one of the other columns has a checkmark? I, therefore, would like the first cell of a row highlighted if there is an "x" anywhere in the rest of the row.
I hope this makes sense.
As explained here when drag copy formula horizontally incrementation happens with anything related to columns. Nothing happens with rows. In order to get an increasing number one can use COLUMN() function in this scenario.
We get row number of original list with a calculation involving COLUMN() function, but then we have to create a string defining our original list's range and get the value in that cell. This is done by INDIRECT() function. (VLOOKUP method not good)
=INDIRECT("$B"&COLUMN()/2)
Now selecting ID and Machinery and dragging right fills all consecutive columns as desired.
Another alternative:
=INDEX($B:$B;COLUMN()/2)
I had a similar problem. sheetSpider does not appear to be available anymore. I tried writing my own script and it kinda worked but it was slow and didn't update reliably when the source data changed.
I eventually solved my problem with a pivot table (Data->Pivot table report...). Just select the range, then add all the columns you want to see as Rows (sounds counterintuitive I know) and set Filters for Column B > 1 and Column D = teacher's name. Create a new pivot table for each teacher on its own sheet where the second filter varies. More details about pivot tables in Google Sheets here. For me it is quick and updates well whenever the source data changes.
Best Answer
The formula you need for range
A1:Ais: