# Having a formula output to a different cell

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?

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), )))`````` 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)``