Hot answers tagged

3

You need =E$1 there. (Or, =$E$1 if the formatted range spans multiple columns.) Putting E1 would compare cells to the string "E1", which is not what you want. Putting =E1 compares the first cell in your column range to E1, the second to E2, the third to E3, etc -- because the reference is relative. The dollar sign makes it an absolute reference.


3

Short answer Add a new sheet. Add the following formulas to the new sheet Cell A2 -> Headers =OFFSET('Form responses 1'!$A$2,ceiling((row()-1)/5,1)-1,0) This could be repeated several times, one for each header column, just change the last parameter accordingly. Cell B2 -> data =OFFSET('Form responses ...


2

It doesn't work with arrayformula - importdata, importxml, and split will not work it unfortunately.


2

Keep the inequality signs going in the same direction: =IF(A1<60, "F", IF(A1<70, "D", IF(A1<80, "C", IF(A1<90, "B", "A")))) The above works fine as long as there aren't too many cases. But if you had many options, C-,C+,B-,B+... it would be better to use a lookup table instead. For example, if you have a lookup table such as the one below, ...


1

Since the content of L7 is going to be overwritten, spreadsheet formulas cannot perform such reset. You need a script that has the spreadsheet keys of all the spreadsheets to be changed, and has the authority to edit them (i.e., the Google account under which it runs has this authority). It can be either a stand-alone script, or bound to your master ...


1

Your draft is pretty good, but there is a design flaw: if you insert the values between B and C, then the column with formulas will become D. So, next time the script will try to get data, it will be looking at a wrong place. Simply put, the source of the data you are recording (i.e., the column with formulas) should stay in the same place. You can put the ...



Only top voted, non community-wiki answers of a minimum length are eligible