Excel 2007 : Linking to a cell in a Sheet using Tab Name on a Current Sheet

Hello All,

I've always found answers to my Excel and other questions via research, however, this issue got me stuck...

I've got a huge workbook with multiple data sheets and one summary sheet. What I need to be able to do is link summary to cells in individual data sheets using a list of those tab names which is located on the summary.

Specifically: say my tab names are "summary", "apples", "oranges", "bananas" and so on. Right now, I have links on my summary such as ='Apples'!K1 and ='Oranges'!L1. I also have a list of tab names on my summary sheet:

Apples (cellA1)
Oranges(cell A2)
etc

I need to figure out if I can use the list of names rather than typing up tab names. Reason (tabs names are more complex than the fruit and there are lots of them)

Thanks!

Stan

Hi Stan;

What you're looking for is the INDIRECT function. It's a bit wonky of a function that takes a little playing around with to understand how it works, but here's the best explanation I can give.

If you have text within a cell, such as having Apples in A1, you can reference the text within the cell, and use it as a cell reference. What? Here's an example:

Sheet1!A1 = Apples
Apples!A1 = 6432

So, if you're on Sheet1, and you type in the formula =Indirect(A1&"!A1"), the result would be 6432.

Is that the functionality you were looking for?