How to change sheet names via formula
 

Hello Everyone!

I have 5 sheets with different city names. On another sheet, called
Compare, the user selects the city, which populates B2.

In yet another sheet, I started with the formula =Compare!B2, which
successfully returned the selected city. However, I don't want the city
name, I only want it as a preface to switch to the appropriate sheet before
grabbing the data.

So I tried =(Compare!B2)!I2, I was hoping that the parentheses would switch
to the sheet that matched the city in B2 and return the value in I2, but no.

Does anyone know how to use a sheet name in a formula?

Thank you for your time.

Arlen

How to change sheet names via formula
 

=INDIRECT("'"&Compare!B2&"'!I2")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Arlen" wrote in message
...
Hello Everyone!

I have 5 sheets with different city names. On another sheet, called
Compare, the user selects the city, which populates B2.

In yet another sheet, I started with the formula =Compare!B2, which
successfully returned the selected city. However, I don't want the city
name, I only want it as a preface to switch to the appropriate sheet
before
grabbing the data.

So I tried =(Compare!B2)!I2, I was hoping that the parentheses would
switch
to the sheet that matched the city in B2 and return the value in I2, but
no.

Does anyone know how to use a sheet name in a formula?

Thank you for your time.

Arlen

How to change sheet names via formula
 

=INDIRECT("'" & Compare!B2 & "'!I2")

HTH,
Bernie
MS Excel MVP


"Arlen" wrote in message
...
Hello Everyone!

I have 5 sheets with different city names. On another sheet, called
Compare, the user selects the city, which populates B2.

In yet another sheet, I started with the formula =Compare!B2, which
successfully returned the selected city. However, I don't want the city
name, I only want it as a preface to switch to the appropriate sheet before
grabbing the data.

So I tried =(Compare!B2)!I2, I was hoping that the parentheses would switch
to the sheet that matched the city in B2 and return the value in I2, but no.

Does anyone know how to use a sheet name in a formula?

Thank you for your time.

Arlen

How to change sheet names via formula
 

Brilliant, guys!

Thanks to both of you.

Arlen

"Bernie Deitrick" wrote:

=INDIRECT("'" & Compare!B2 & "'!I2")

HTH,
Bernie
MS Excel MVP


"Arlen" wrote in message
...
Hello Everyone!

I have 5 sheets with different city names. On another sheet, called
Compare, the user selects the city, which populates B2.

In yet another sheet, I started with the formula =Compare!B2, which
successfully returned the selected city. However, I don't want the city
name, I only want it as a preface to switch to the appropriate sheet before
grabbing the data.

So I tried =(Compare!B2)!I2, I was hoping that the parentheses would switch
to the sheet that matched the city in B2 and return the value in I2, but no.

Does anyone know how to use a sheet name in a formula?

Thank you for your time.

Arlen