Referencing a worksheet in an equation 2

[Drew08]

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.

 

[IFRs]

Type = and then navigate to the cell you want and the npress ENTER.

 

[Denial]

I haven't confirmed this, but I think that the INDIRECT function will do what you want.

 

[swertel]

Yes, Indirect is how you get a cell location based on a value within a different cell, so it is the starting place for the equation you want to use.

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

 

[electricpete]

I want to define it in a cell and refer to it.

That is a useful tool to learn - makes your spreadsheets more readable and self-documenting.

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.

 

[electricpete]

Personally I think using the above technique where the name is visible to the left makes the spreadsheet easier to read than other means of naming range where the name doesn't appear anywhere other than the the names dialogue.

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.

 

[Drew08]

Thanks all,

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.