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

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)

--
Garry

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

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 at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Sep 11, 7:15*pm, GS <g...@somewhere.net> wrote:
> 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.

floppyzedolfin has brought this to us :
> On Sep 11, 7:15*pm, GS <g...@somewhere.net> wrote:
>> 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.

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

--
Garry

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

On 13 sep, 01:05, GS <g...@somewhere.net> wrote:
> floppyzedolfin has brought this to us :
>
>
>
>
>
>
>
>
>
> > On Sep 11, 7:15*pm, GS <g...@somewhere.net> wrote:
> >> 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.
>
> 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

On 13 sep, 01:05, GS <g...@somewhere.net> wrote:
> floppyzedolfin has brought this to us :
>
>
>
>
>
>
>
>
>
> > On Sep 11, 7:15*pm, GS <g...@somewhere.net> wrote:
> >> 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.
>
> 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