Ask Your Question
1

I want to concatenate an =, a cell value with a number and a letter to make a cell reference formula [closed]

asked 2017-05-09 23:26:49 +0200

Biker60 gravatar image

Cell D3 has a value of 3 Cell A3 has a value of 047853002508

In cell I3 I used the following formula: =CONCATENATE("=A",D3)

The result is cell I3 has a value =A3. I want the value to be 047853002508 which would be the result of the cell reference =A3. If I type =A3, the result is 047853002508.

Why doesn't the concatenate function treat the result as a cell reference?

I assume I'm doing something wrong and I hope I explained this correctly.

Thanks.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Biker60
close date 2017-05-10 00:28:08.697836

2 Answers

Sort by » oldest newest most voted
1

answered 2017-05-10 00:04:44 +0200

librebel gravatar image

Hello Biker60, you could try:

=INDIRECT(CONCATENATE("A";D3) ;1)

( change the semicolons to comma's in your case )

edit flag offensive delete link more

Comments

Both versions of the INDIRECT statement worked. Thanks erAck and librebel. I have about 1000 rows of data where I'll apply this formula. It will save me a lot of time.

Biker60 gravatar imageBiker60 ( 2017-05-10 00:25:47 +0200 )edit
1

answered 2017-05-10 00:04:54 +0200

erAck gravatar image

CONCATENATE concatenates strings of its arguments, if an argument is a cell reference then that is dereferenced and the result concatenated, here D3 is "3" so at the end you get the string "=A3".

You're probably looking for INDIRECT instead, if you want to display the value of the content of A3 in this example. So =INDIRECT("A"&D3) which is the same as =INDIRECT(CONCATENATE("A",D3))

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2017-05-09 23:26:49 +0200

Seen: 1,470 times

Last updated: May 10 '17