Accessing the value in a cell of a sheet of variable name

[floppyzedolfin]

Hello,
I am in this situation:

"Information sheet" contains a cell (say B11) in which the user will
enter the option (either "3" or "4").
Two sheets, "Play options 3" and "Play options 4" contain the
settings. For instance, cell B12 of these sheets contain "Roll a die"
on "Play options 1" and "Draw a card" on "Play options 2".

In a fourth sheet, I want to display the content of that cell A9
depending on the option chosen.

So far, I tried using:

=INDIRECT("'Play options " & 'Information Sheet'!B11 & "'!A9")

but this performs weirdly:

The file I'm working on contained only one "Play options" sheet, and
I'm updating the macros to be able to use 3 or 4 players. Renaming
sheet "Play options" to "Play options 4" updated the macros, from
='Play options'!A9
to
='Play options 4'!A9
and the =INDIRECT macro above returns the proper value when the option
is set to 4, but not to 3.
Sheet "Play options 3" was created as a copy of "Play Options 4", and
was then renamed.


Where did I do something wrong? Was it during the copy?
I'm using Excel 10.

Thanks

 

[GS]

floppyzedolfin has brought this to us :

Hello,
I am in this situation:

"Information sheet" contains a cell (say B11) in which the user will
enter the option (either "3" or "4").
Two sheets, "Play options 3" and "Play options 4" contain the
settings. For instance, cell B12 of these sheets contain "Roll a die"
on "Play options 1" and "Draw a card" on "Play options 2".

In a fourth sheet, I want to display the content of that cell A9
depending on the option chosen.

So far, I tried using:

=INDIRECT("'Play options " & 'Information Sheet'!B11 & "'!A9")

but this performs weirdly:

The file I'm working on contained only one "Play options" sheet, and
I'm updating the macros to be able to use 3 or 4 players. Renaming
sheet "Play options" to "Play options 4" updated the macros, from
='Play options'!A9
to
='Play options 4'!A9
and the =INDIRECT macro above returns the proper value when the option
is set to 4, but not to 3.
Sheet "Play options 3" was created as a copy of "Play Options 4", and
was then renamed.


Where did I do something wrong? Was it during the copy?
I'm using Excel 10.

Thanks

Instead of using a cell address, why not give the cell a local defined
name so it can be the same name for that cell on every sheet? Then,
just have the code ref: Sheets(<whatever>).Range(ChosenOption)

 

[GS]

To give A9 the same name on every sheet...

1. Open the Define name dialog
2. In the Name box, type the sheetname as follows:
'Play Options 1'!ChosenOption
Note the name is wrapped in apostrophes and followed by "!"
3. In the RefersTo box, type =$A$9
4. Click 'OK'

Repeat this for each sheet you want to use this name on.


Optional method:

1. Select A9 on each sheet
2. In the namebox left of the Formula Bar,
type the sheetname as shown above
3. Press the 'Enter' key when done

 

[floppyzedolfin]

To give A9 the same name on every sheet...

  1.  Open the Define name dialog
  2.  In the Name box, type the sheetname as follows:
      'Play Options 1'!ChosenOption
      Note the name is wrapped in apostrophes and followed by "!"
  3.  In the RefersTo box, type  =$A$9
  4.  Click 'OK'

Repeat this for each sheet you want to use this name on.

Optional method:

  1.  Select A9 on each sheet
  2.  In the namebox left of the Formula Bar,
        type the sheetname as shown above
  3.  Press the 'Enter' key when done

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Well, that can be usefull, but I'm going to use several options. Like,
a hundred. A-Cx9-150. I can't imagine myself renaming all these cells
twice
It's reference is the same on each sheet - it's A9 on both Play
options 3 and Play options 4.

 

[GS]

floppyzedolfin has brought this to us :

Well, that can be usefull, but I'm going to use several options. Like,
a hundred. A-Cx9-150. I can't imagine myself renaming all these cells
twice
It's reference is the same on each sheet - it's A9 on both Play
options 3 and Play options 4.

And so you just name A9 on both those sheets. Why do you think you need
to 'rename' them?

 

[Frederic LE GUEN - MVP Excel]

floppyzedolfin has brought this to us :











And so you just name A9 on both those sheets. Why do you think you need
to 'rename' them?

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Hi

Your solution with the INDIRECT function is correct but you don't use
it properly.
Please, follow this link, and you will have a better approach of the
INDIRECT function
http://www.excel-exercice.com/en/function-indirect

Regards

 

[Frederic LE GUEN - MVP Excel]

floppyzedolfin has brought this to us :











And so you just name A9 on both those sheets. Why do you think you need
to 'rename' them?

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Hi

Your solution with the INDIRECT function is correct but you don't use
it properly.
Please, follow this link, and you will have a better approach of the
INDIRECT function
http://www.excel-exercice.com/en/function-indirect

Regards