Name Defining

[Guest]

Howcome when I go to add a new name in the name--->define box, excel
automatically adds the sheet name to my formula? I'm trying to run a macro by
using the name define function but it won't work for different workbooks
because they all have different sheet names and hence aren't referenced
properly. Is there a way to get around this?


For example this is the formula that I'm entering in the name define box (I
know its huge)

=OFFSET(INDIRECT("$A$"&(MATCH("Run",$A:$A,0)+1)),0,0,(COUNTIF($A:$A,1)+COUNTIF($A:$A,2)),3)

and here's what excel is automatically doing..........

=OFFSET(INDIRECT("$A$"&(MATCH("Run",Testing_Macro!$A:$A,0)+1)),0,0,(COUNTIF(Testing_Macro!$A:$A,1)+COUNTIF(Testing_Macro!$A:$A,2)),3)

 

[Jim Rech]

Try more INDIRECTs. Whether this will work in practice you'll have to
investigate:

=OFFSET(INDIRECT("$A$"&(MATCH("Run",INDIRECT("$A:$A"),0)+1)),0,0,(COUNTIF(INDIRECT("$A:$A"),1)+COUNTIF(INDIRECT("$A:$A"),2)),3)

--
Jim
| Howcome when I go to add a new name in the name--->define box, excel
| automatically adds the sheet name to my formula? I'm trying to run a macro
by
| using the name define function but it won't work for different workbooks
| because they all have different sheet names and hence aren't referenced
| properly. Is there a way to get around this?
|
|
| For example this is the formula that I'm entering in the name define box
(I
| know its huge)
|
|
=OFFSET(INDIRECT("$A$"&(MATCH("Run",$A:$A,0)+1)),0,0,(COUNTIF($A:$A,1)+COUNTIF($A:$A,2)),3)
|
| and here's what excel is automatically doing..........
|
|
=OFFSET(INDIRECT("$A$"&(MATCH("Run",Testing_Macro!$A:$A,0)+1)),0,0,(COUNTIF(Testing_Macro!$A:$A,1)+COUNTIF(Testing_Macro!$A:$A,2)),3)
|
|

 

[Guest]

It worked, you're a life saver!

Thank you