Ask Your Question

how to add strings to address?

asked 2020-05-23 00:50:06 +0100

Math3333 gravatar image
 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.
edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2020-05-23 01:10:20 +0100

Opaque gravatar image

updated 2020-05-23 01:11:43 +0100


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.

edit flag offensive delete link more


@Opaque, 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?

Math3333 gravatar imageMath3333 ( 2020-05-23 01:23:31 +0100 )edit

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.

Opaque gravatar imageOpaque ( 2020-05-23 01:27:34 +0100 )edit

If you are referencing cells on the same row as the formula, you can substitute ROW() for "2". Elaborating on @Opaque's answer:

=INDIRECT("$Weekly." & A49 & ROW() )

If you reference across rows, this needs some adjustment. Please provide details.

keme gravatar imagekeme ( 2020-05-23 01:41:00 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-05-23 00:50:06 +0100

Seen: 29 times

Last updated: May 23 '20