 `````` I have this as an address \$Weekly.\$P2, where column letter changes according to cell A49.
A49 contents change to other column letters. How to add contents of A49 to \$Weekly.\$(A49)2?
I have tried to use INDIRECT. \$Weekly.\$INDIRECT(A49)&"2", but I get an error.``````

Hello,

the whole reference must be built as text string and then inserted as a parameter to `INDIRECT()`. Hence use:

`=INDIRECT("\$Weekly." & A49 & "2")` (this requires in `A49` something like `\$P`)

Hint: To test such reference, just type the string into a cell (here: `="\$Weekly." & A49 & "2"` and if it yields a valid reference, then it is a valid candidate for being used with `INDIRECT()`

Hope that helps.

If the answer helped to solve your problem, please click the check mark ( ) next to the answer.

@anon73440385, it’s works for the first cell, but does not work for scrolling. “2” does not change to 3 and so on…
It is possible to make the row parameter update, while dragging?

You immediately got hit by the drawback of using `INDIRECT()` and you did not mention, that the formula needs to be correctly updated on dragging. The only thing you could do is something like:
`=INDIRECT("\$Weekly." & A49 & ROW()+n)` where `n` depends on your starting row: Meaning:

If you start your formula e.g. in row `2` then `n=0` (since `(ROW()+0)=2`
If you start your formula e.g. in row `3` then `n=-1` (since `(ROW()-1)=2`

If you start your formula e.g. in row `20` then `n=-18` (since `(ROW()-18)=2`
and so on. This way you can drag down, since `ROW()` always yields the current row number. You only need to assure the correct offset using a proper `n` value.

If you are referencing cells on the same row as the formula, you can substitute `ROW()` for “2”.
``````=INDIRECT("\$Weekly." & A49 & ROW() )