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)
|
|