4

I do not want to concatenate a string or show the value like "Some Text " & C12. I need to use the text in a cell as part of the formula in another cell. For Example:

I have a cell containing a date 17.02.2014. I need to use that date in a formula within another cell:

='17.02.2014'!$AV$6  

However this date needs to be pulled dynamically, for this example from E26. I would have expected it to look something like:

='&E26&'!$AV$6 

What is the correct syntax?

5

Please try:

 =indirect(E26&"!"&$AV$6)  

Indirect is able to read a string as a cell reference. Within the parentheses three elements are concatenated: the content of E26 (which could be as here a sheet named 17.02.2014), the required "!" and the content of AV6 (anchored with $s so it does not change if the formula is copied around).

1

I just landed here with this issue and the solution was still giving errors. Example:

  A                B                      C
1| String        | INDIRECT()             | Result 
-+---------------+------------------------+-------------
2| fred          | =INDIRECT(A2)&"rick"   | #REF
3| fred          | =INDIRECT("A2")&"rick" | fredrick

So the instructions "must be and actual string" meant for me that the string is the string "A2" that represents the cell.

I hope it helps the next bloke or darling that comes upon this need.

Unfortunate thing about this is it don't scrape down, auto magically update the cells. So it's a pest.

Update: This works with dragging down on many cells

    A             B                        C
1| String  | INDIRECT()               | Result 
-+---------+--------------------------+-------------
2|non      | =INDIRECT("A2:A")&"stop" |nonstop
3|short    | =INDIRECT("A2:A")&"stop" |shortstop
4|pit      | =INDIRECT("A2:A")&"stop" |pitstop
5|door     | =INDIRECT("A2:A")&"stop" |doorstop
6|back     | =INDIRECT("A2:A")&"stop" |backstop
1

The INDIRECT function will only accept a range, as explained in the Drive Help: enter image description here

The thing with functions like NOW() and TODAY() is, that they do just that !!

The way to go is by referencing indirectly, like shown in the screenshot (C5 vs. C2).

Screenshot

enter image description here

Example

I've created an example file for you: How to Use Text from a cell in a formula

Reference

https://support.google.com/drive/answer/3093377

-2

Code example for single cell (B1 containing a number):

="Quick numbers from "&indirect("B1")&" projects"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.