The usual way to reference a sheet is here but how do you reference a sheet when the sheet’s name is a value in a cell?

For example,

`A1 = "Sheet number two" B1 = A1&"!B4"`

Here I want B1 to display the value in

`'Sheet number two'!B4`

but it’s not working for me. Any ideas?

**Answer**

You need to use the `indirect()`

formula. So in cell `B1`

you need to put this formula:

```
=indirect(A1&"!B4")
```

**Attribution***Source : Link , Question Author : E.Beach , Answer Author : Jacob Jan Tuinstra*