×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Referencing a worksheet in an equation
2

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.   
 

RE: Referencing a worksheet in an equation

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

RE: Referencing a worksheet in an equation

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

RE: Referencing a worksheet in an equation

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

RE: Referencing a worksheet in an equation

2

Quote:

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.

RE: Referencing a worksheet in an equation

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.

RE: Referencing a worksheet in an equation

(OP)
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.  
  

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources