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)+C OUNTIF(Testing_Macro!$A:$A,2)),3)

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)+COUNT IF(INDIRECT("$A:$A"),2)),3)

--
Jim
"Chris" wrote in message
...
| 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)+C OUNTIF(Testing_Macro!$A:$A,2)),3)
|
|

It worked, you're a life saver!

Thank you

"Jim Rech" wrote:

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)+COUNT IF(INDIRECT("$A:$A"),2)),3)

--
Jim
"Chris" wrote in message
...
| 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)+C OUNTIF(Testing_Macro!$A:$A,2)),3)
|
|