Name Defining
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) |
Name Defining
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) | | |
Name Defining
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) | | |
All times are GMT +1. The time now is 10:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com