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!

Hi Chris

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

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


--
Regards

Roger Govier


"ChrisP" wrote in message
...
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!

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:

Hi Chris

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

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


--
Regards

Roger Govier


"ChrisP" wrote in message
...
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!

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.

--
Regards

Roger Govier


"ChrisP" wrote in message
...
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:

Hi Chris

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

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


--
Regards

Roger Govier


"ChrisP" wrote in message
...
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!