Dynamic reference to another sheet?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
=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"))
 
Back
Top