Dynamic reference to another sheet?

Hi,

My problem is this

Column A (let's say A2) has a number updated via web query (3-digits, say
901). I have also named all the sheets in the workbook after the possible
numbers that A can show (odd numbers from 901-931). In B2 I have a formula
that does a calculation based on data from another sheet, and that sheets
name corresponds to the number of A2. Any chance of building in a cell-value
in the reference?

=OM('901'!D18-'901'!J18>1;'901'!Y28;OM('901'!D18-'901'!J18<-1;'901'!Y7;"Please Help"))

This is how I have it set up now with the '901's being static. I would like
this reference to check A2 for a value (say 909) and put this value in where
the '901's is now and that would refer to a sheet with the same name.


Sorry about the mess, but if someone can understand this any help would be
greatly appreciated.

Regards

Mitch

Mitch

The formula you are looking for is:

=OM(indirect(a2&"!"&"D18")-indirect(a2&!"&"J18")>1;indirect(a2&"!"&"Y28");OM(indirect(a2&"!"&D18)-indirect(a2&"!"&"J18")<-1;indirect(a2&"!"&"Y7");"Please
Help"))

I havn't error checked this and I'm nit sure what the OM refers to (maybe a
later version of Excel) but use the indirect formula as I have written it and
it should work.

HTH

Ian

"Mitch" wrote:

> Hi,
>
> My problem is this
>
> Column A (let's say A2) has a number updated via web query (3-digits, say
> 901). I have also named all the sheets in the workbook after the possible
> numbers that A can show (odd numbers from 901-931). In B2 I have a formula
> that does a calculation based on data from another sheet, and that sheets
> name corresponds to the number of A2. Any chance of building in a cell-value
> in the reference?
>
> =OM('901'!D18-'901'!J18>1;'901'!Y28;OM('901'!D18-'901'!J18<-1;'901'!Y7;"Please Help"))
>
> This is how I have it set up now with the '901's being static. I would like
> this reference to check A2 for a value (say 909) and put this value in where
> the '901's is now and that would refer to a sheet with the same name.
>
>
> Sorry about the mess, but if someone can understand this any help would be
> greatly appreciated.
>
> Regards
>
> Mitch

I knew I shouldn't have tried to write the whole thing as I've made an error.
This should work:

=OM(indirect(a2&"!"&"D18")-indirect(a2&!"&"J18")>1;indirect(a2&"!"&"Y28");OM(indirect(a2&"!"&"D18")-indirect(a2&"!"&"J18")<-1;indirect(a2&"!"&"Y7");"Please
Help"))

Ian

"Ian P" wrote:

> Mitch
>
> The formula you are looking for is:
>
> =OM(indirect(a2&"!"&"D18")-indirect(a2&!"&"J18")>1;indirect(a2&"!"&"Y28");OM(indirect(a2&"!"&D18)-indirect(a2&"!"&"J18")<-1;indirect(a2&"!"&"Y7");"Please
> Help"))
>
> I havn't error checked this and I'm nit sure what the OM refers to (maybe a
> later version of Excel) but use the indirect formula as I have written it and
> it should work.
>
> HTH
>
> Ian
>
> "Mitch" wrote:
>
> > Hi,
> >
> > My problem is this
> >
> > Column A (let's say A2) has a number updated via web query (3-digits, say
> > 901). I have also named all the sheets in the workbook after the possible
> > numbers that A can show (odd numbers from 901-931). In B2 I have a formula
> > that does a calculation based on data from another sheet, and that sheets
> > name corresponds to the number of A2. Any chance of building in a cell-value
> > in the reference?
> >
> > =OM('901'!D18-'901'!J18>1;'901'!Y28;OM('901'!D18-'901'!J18<-1;'901'!Y7;"Please Help"))
> >
> > This is how I have it set up now with the '901's being static. I would like
> > this reference to check A2 for a value (say 909) and put this value in where
> > the '901's is now and that would refer to a sheet with the same name.
> >
> >
> > Sorry about the mess, but if someone can understand this any help would be
> > greatly appreciated.
> >
> > Regards
> >
> > Mitch

=OM(INDIRECT("'"&A2&"'!D18")-INDIRECT("'"&A2&"'J18")>1;
INDIRECT("'"&A2&"'!Y28");OM(INDIRECT("'"&A2&"'!D18")-
INDIRECT("'"&A2&"'!J18")<-1;INDIRECT("'"&A2&"'Y7");"Please Help"))


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Mitch" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> My problem is this
>
> Column A (let's say A2) has a number updated via web query (3-digits, say
> 901). I have also named all the sheets in the workbook after the possible
> numbers that A can show (odd numbers from 901-931). In B2 I have a formula
> that does a calculation based on data from another sheet, and that sheets
> name corresponds to the number of A2. Any chance of building in a
cell-value
> in the reference?
>
>
=OM('901'!D18-'901'!J18>1;'901'!Y28;OM('901'!D18-'901'!J18<-1;'901'!Y7;"Plea
se Help"))
>
> This is how I have it set up now with the '901's being static. I would
like
> this reference to check A2 for a value (say 909) and put this value in
where
> the '901's is now and that would refer to a sheet with the same name.
>
>
> Sorry about the mess, but if someone can understand this any help would be
> greatly appreciated.
>
> Regards
>
> Mitch