Google-sheets – Having a formula output to a different cell

formulasgoogle sheetsgoogle-sheets-arrayformulasortingvlookup

I have a Google Sheet in the following format:

  ID |  VAL
-------------
1    | dataH
2    | dataW
3    | dataX
4    | dataC
5    | dataG
6    | dataL
...  | ...

ID and VAL are on cells A1 and B1, respectively.

In A2, I have the following formula: =ArrayFormula(if(ISBLANK(B2:B), "",ROW(A2:A)-1)), which puts in a number in the ID column if there is data in the VAL column.

However, I'd like to be able to sort data, for example from A to Z, while keeping ID constant. However, when I sort VAL, the cell containing the formula, ID 1, gets dragged down to its proper position, but then leaves all cells in the column above it blank.

I have 2 possible solutions: somehow have the formula in A1 (ID) and display ID instead of 1; or have the formula in C1 and output the results starting from A2.

Is there a way to do one of the 2 solutions above?

Best Answer

somehow have the formula in A1 (ID) and display ID

=ARRAYFORMULA(IF(ROW(A1:A)=1, "ID", IF(LEN(B1:B), ROW(A1:A)-1, )))

However, this will not solve your issue. The best you can do is to turn the output of array formula into static values via CTRL + C and then re-pasting it with CTRL + SHIFT + V

Also, the ID could be assigned already beforehand so when you sort it, it will act like static:

=ARRAYFORMULA(IF(ROW(A1:A)=1, "ID", IF(LEN(B1:B), 
 VLOOKUP(B1:B, {SORT(B1:B), ROW(A1:A)}, 2, 0), )))

0

If none of the above is acceptable you will need to have formula per cell... paste in A2 cell and drag down:

=IF(LEN(B2), ROW()-1)

Related Topic