Cell reference

[Guest]

hi

How do I write a cell reference to another sheet, using another cellvalue.
Example
Instead of writing ='sheet2'!A1
Id like to set the name of the "sheet" using the value of another cell.
Seems easy enough but I cant seem to get the syntax right.
Thanks!

 

[Roger Govier]

Hi Chris

=INDIRECT("'"&X1&"'!A1)
where X1 holds the worksheet name

Note that the quotes are " ' " and " ' ! A1"

 

[Guest]

thanks alot! worked like a charm.
This created a new problem thol.... is there a quick way to copy this to 100
cells in 1 collumn and increasing A1 by 1.
If I ctrl drag the cell it increases X1 by



"Roger Govier" skrev:

 

[Roger Govier]

Hi Chris

Make the X1 absolute by using $X$1

Note the A1 will remain fixed, as it is inside of the quotes.
If you did want the cell reference to alter as you copied down, then you
would need to use

=INDIRECT("'"&$X$1&"'!A"&ROW(A1))
Row(A1) will return 1, but when dragged down it will change to row(A2)
and return 2 and so on, hence the reference will change to A1, A2 etc.