Dynamic reference to another sheet?

M

Mitch

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

Ian P

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
 
I

Ian P

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
 
B

Bob Phillips

=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 said:
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?
Click to expand...
=OM('901'!D18-'901'!J18>1;'901'!Y28;OM('901'!D18-'901'!J18<-1;'901'!Y7;"Plea
se Help"))
 
Top