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.
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.
Asked: 2020-05-23 00:50:06 +0200
Seen: 18 times
Last updated: May 23
Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.
