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'!J181;'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

Dynamic reference to another sheet?
 

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'!J181;'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

Dynamic reference to another sheet?
 

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'!J181;'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

Dynamic reference to another sheet?
 

=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" wrote in message
...
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'!J181;'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