Cell References Across Different Sheets

[Lisa517]

I hope this isn't a duplicate question; to describe what I'm trying to do keeps getting so detailed that searches aren't very effective. Please feel free to point me in the right direction if this has already been asked and answered.

I'm working with a book that has one sheet at the front listing multiple properties, and a separate tab for more detailed information of each property. Obviously, I'd like for some of the fields on my front page to populate automatically with the corresponding information from the correct detail tab. However, I will be adding and removing these detail tabs frequently, and don't want to have to insert the correct cell reference each time I add something new or take something out.

So my question is: If the name of the tab I want is already on my front page, can I create my cell reference in a another cell on that front page so that it automatically goes to the specified cell within the sheet that matches the name I specify?

Example:
On my Cover Tab, in column A, I'm listing the property names. In column B, for each property, I'd like to return the value in cell B4 of the Property Detail Tab with the same name as the property in column A of the Cover Tab.

So if row 1 on the Cover Tab is supposed to be information for Twin Oaks, and there is a Property Detail Tab named "Twin Oaks," I want my formula in column B on the Cover Tab to automatically go to the "Twin Oaks" tab to get the information in cell B4 of that tab. If row 2 on the Cover is for Lone Oak, I want to be able to enter "Lone Oak" in column A, the drag down the formula in column B from row 1 and have it recognize that I want the same cell reference displayed, but this time from tab "Lone Oak."

Thanks in advance for any help with this!

 

[Snakehips]

Lisa517,

Welcome to MrExcel.

If e.g. "Lone Oak" in cell A2 then formula in B2 .....

=IFERROR(INDIRECT("'"&A2&"'!B4"),"")

to get value in Lone Oak B4

Hope that helps.

 

[Lisa517]

It certainly did! Can you explain the formula a little bit so I understand it better for future use and modification? I've never used those two particular functions before.

 

[Lisa517]

Oh, and THANK YOU! I was so excited to find the perfect formula on the first response so quickly that I forgot to say it!

 

[Snakehips]

You are welcome.
Rather than me pen my own explanation, take a look at these two links.....

Excel IFERROR Function


http://www.contextures.com/xlFunctions05.html


There will be loads of others if you care to Google.

 

[Lisa517]

Thanks, I was hoping someone would narrow down for me some of the best options. There are so many to wade through!