# Dynamically form a cell value in Google Sheets

This might be very trivial, but I was not able to obtain any concrete result from my Google searches, so asking here.

I want to fill a column with the content of some other column (in some other sheet). The value at the existing place is not organised to the current requirements, so I just can't directly assign it to the new column. I have another column that specifies the ordering for the new column. So, I want to create my new column with the help of this ordering.

In short, I was wishing to assign my new column as `sheet1!A\${sheet2!A1}` (this doesn't work).

Say, if I have value 23 at `sheet2!A1`, then the corresponding value in the new column should be of the cell `sheet1!A23`.

• Welcome. Please remember that as per site guidelines when an answer addresses your question, you should accept it and even upvote it so others can benefit as well. Apr 18, 2021 at 11:41

You need to use the INDIRECT function

Try the following in cell `A1` of `Sheet1`

``````=INDIRECT("Sheet2!A"&A1)
``````

The result would be the value of cell `A23` of `Sheet1` in cell `A1` in `Sheet1`

This sort of thing is usually done with `sort()`, as in:

`=sort(Sheet1:A1:A, Sheet2:A1:A, true)`

This is an array formula that creates the whole result column in one go.

In your case, things are made a little more complex by the fact that the data column and the index column are in different sheets. This will pose a problem in the event the number of rows differs between the two sheets.

The easy solution is to move the index column to the same sheet as the data column. If that is not possible for some reason, you can constrain the two columns to the same height like this:

``````=sort(
array_constrain(Sheet1:A1:A, min(rows(Sheet1:A1:A), rows(Sheet2:A1:A)), 1),
array_constrain(Sheet2:A1:A, min(rows(Sheet1:A1:A), rows(Sheet2:A1:A)), 1),
true
)
``````

If you need more help, please share a publicly editable sample spreadsheet with realistic-looking data.