Referencing a worksheet in an equation
Referencing a worksheet in an equation
(OP)
Is there a way to refer to a worksheet in the current workbook that is listed in a cell on a different sheet?
Say I want to retrieve the value of Cell A1 of Worksheet Sheet 2. From Sheet 1, I would write the equation =Sheet 2!A1. Except rather than typing "Sheet 2", I want to define it in a cell and refer to it.
If I have the text "Sheet 2" in cell A1 of Sheet 1, I want to write ='A1'!A1 in Sheet 1. But that doesn't work.
Say I want to retrieve the value of Cell A1 of Worksheet Sheet 2. From Sheet 1, I would write the equation =Sheet 2!A1. Except rather than typing "Sheet 2", I want to define it in a cell and refer to it.
If I have the text "Sheet 2" in cell A1 of Sheet 1, I want to write ='A1'!A1 in Sheet 1. But that doesn't work.





RE: Referencing a worksheet in an equation
RE: Referencing a worksheet in an equation
RE: Referencing a worksheet in an equation
You may also have to concatenate an equation in order to get the entire cell address. Been a while since I've used INDIRECT and, sorry, but don't have time to work out the details so I'm going by memory.
--Scott
http://wertel.eng.pro
RE: Referencing a worksheet in an equation
Insert / Name / Define gives a dialogue box to name a range or cell.
An even slicker technique that I like to use is to put the name and value in two adjacent cells
A1 = Myname B1 = Mydata
Then highlight A1:B1 and select Insert / Name / Create / Left-Column.
Now you can refer to cell B1 contents anywhere in the workbook by MyName (i.e. 2*sqrt(MyName) )
Also you can organize you constants in unison - put all the names in column A, all the values / formula's in column B, highlight the entire range A1:B20 and repeat Insert /Name/Create/Left-Columns.
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Referencing a worksheet in an equation
I do a lot of big spreadsheet projects and named ranges are indispensable.
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Referencing a worksheet in an equation
The INDIRECT function was exactly what I was looking for. I used the equation =INDIRECT(A1&"!A1")where the name of the sheet was in the current sheet cell A1.
Giving a name to the cell also worked, but assuming I have a table with a million rows, in which I want to refer to a million sheets, I don't want to name a million cells...exaggeration of course.
I have read several posts lately and like the naming method. I certainly see its value, and I'm trying to employ it more often.