Cell reference
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! |
Cell reference
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! |
Cell reference
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! |
Cell reference
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! |
All times are GMT +1. The time now is 04:33 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com