There HAS to be a way to do this...

[Guest]

'Sheet 1' I have the number 5 in cell A1

On 'Sheet 2' I can make a formula in A1 that says:
='Sheet 1'!A1
and it will return the number 5. Easy enough.

But what if, on Sheet 2 I have the words "Sheet 1" in another cell, so that
I could reference the sheet name in cell A1 instead of typing it out? In
other words, have:
=A5!A1 (where A5 would have the name of the worksheet "Sheet 1")

This is a very simple example, but I have numerous columns of data, each
column pulling info from a different sheet. I have each sheet name at the top
of the column, so I want to make one formula that will reference the column
title for thte sheet name so that I can copy the formula all the way across
the columns, without having to edit each column and hand-typing in the sheet
name. Am I making sense.? This seems so simple, but I cannot figure it out,
and not sure if it's possible. Just like there is no function built in to
spit out the tab name. Sometimes I think they forget the simplest of things
when writing these programs.

 

[Guest]

Try this:

On Sheet 2
A5: Sheet 1
A1: =INDIRECT("'"&A5&"'!A1")

Note: In case your sheet name contains any spaces, the formula puts single
quotes (') around it.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Guest]

This did it exactly. Thank you. What's funny is I was searching around yahoo
on some sites, and people were posing this exact same problem back in 2005,
and nobody could answer it. This one site
http://www.officehelp.in/showthread.php?t=763304 had something close to
yours, but the guy said it didn't work. It was =INDIRECT("Sheet"&A1&"!C5")
but it only worked if the sheet names started with "Sheet" obviously.

Thanks again.