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

[Mike]

'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.

[Ron Coderre]

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


"Mike" wrote:

> '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.

[Mike]

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.


"Ron Coderre" wrote:

> 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
>
>
> "Mike" wrote:
>
> > '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.