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”.
Elaborating on @anon73440385’s answer:
=INDIRECT("$Weekly." & A49 & ROW() )
If you reference across rows, this needs some adjustment. Please provide details.