|
My Dynamic Named Range isn't working
Hope someone can help with this issue.
I have a macro that is adding a person to an indexed range. Everything is successful in adding and redexing the Data range except....in the macro and for this test excercise I have a copy and paste from another cell in the worksheet of Dates the value of "1"into the Data range named Data. I have even left a blank column inbetween the Data range and the row index for the index formula [Tim Smith Example]. I was thinking that the blank column would set up my dynamic range well but instead I am getting the results [2nd Tim Smith Example]. Can anyone see what I am doing wrong? I assume it is something in the formula that I do not understand. Thanks in advance. 46 Row Tim Smith Index 0 1 =OFFSET(Dates!$E$3,0,0,COUNTA(Dates!$E:$E),COUNTA( Dates!$3:$3)) 46 Row Tim Smith Index 1 1 1 1 1 1 -- Message posted via http://www.officekb.com |
My Dynamic Named Range isn't working
That wasn't helpful as I have already been to the help index.
Don Guillett wrote: Look in the help index for COUNTA to see your problem Hope someone can help with this issue. [quoted text clipped - 22 lines] 1 1 1 1 1 1 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200802/1 |
My Dynamic Named Range isn't working
46 Row
Tim Smith Index 1 1 1 1 1 1 I have no idea what that's supposed to represent.... Try this: =OFFSET(Dates!$E$3,0,0,COUNTA(Dates!$E$3:$E$65536) ,COUNTA(Dates!$E$3:$IV$3)) -- Biff Microsoft Excel MVP "Carrie_Loos via OfficeKB.com" <u34134@uwe wrote in message news:801fe075342aa@uwe... That wasn't helpful as I have already been to the help index. Don Guillett wrote: Look in the help index for COUNTA to see your problem Hope someone can help with this issue. [quoted text clipped - 22 lines] 1 1 1 1 1 1 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200802/1 |
My Dynamic Named Range isn't working
COUNTA See Also Counts the number of cells that are not empty and -- Don Guillett Microsoft MVP Excel SalesAid Software "Carrie_Loos via OfficeKB.com" <u34134@uwe wrote in message news:801fe075342aa@uwe... That wasn't helpful as I have already been to the help index. Don Guillett wrote: Look in the help index for COUNTA to see your problem Hope someone can help with this issue. [quoted text clipped - 22 lines] 1 1 1 1 1 1 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200802/1 |
My Dynamic Named Range isn't working
Then it's not dynamic, right?
T. Valko wrote: 46 Row Tim Smith Index 1 1 1 1 1 1 I have no idea what that's supposed to represent.... Try this: =OFFSET(Dates!$E$3,0,0,COUNTA(Dates!$E$3:$E$65536 ),COUNTA(Dates!$E$3:$IV$3)) That wasn't helpful as I have already been to the help index. [quoted text clipped - 5 lines] 1 1 1 1 1 1 -- Message posted via http://www.officekb.com |
My Dynamic Named Range isn't working
Then it's not dynamic, right?
T. Valko wrote: 46 Row Tim Smith Index 1 1 1 1 1 1 I have no idea what that's supposed to represent.... Try this: =OFFSET(Dates!$E$3,0,0,COUNTA(Dates!$E$3:$E$65536 ),COUNTA(Dates!$E$3:$IV$3)) That wasn't helpful as I have already been to the help index. [quoted text clipped - 5 lines] 1 1 1 1 1 1 -- Message posted via http://www.officekb.com |
My Dynamic Named Range isn't working
Yes, I see, thanks - Is there any other solution to a dynamic range? Since
the range is picking up 3 extra columns can I put another offset -3 somewhere? Don Guillett wrote: COUNTA See Also Counts the number of cells that are not empty and That wasn't helpful as I have already been to the help index. [quoted text clipped - 5 lines] 1 1 1 1 1 1 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200802/1 |
My Dynamic Named Range isn't working
instead of counta( etc use
match(9999999,$3:$3)-5 and check by touching the f5 key and typing in the name of the defined range if text use "zzzzzzzzzz" -- Don Guillett Microsoft MVP Excel SalesAid Software "Carrie_Loos via OfficeKB.com" <u34134@uwe wrote in message news:802027b0c3f82@uwe... Yes, I see, thanks - Is there any other solution to a dynamic range? Since the range is picking up 3 extra columns can I put another offset -3 somewhere? Don Guillett wrote: COUNTA See Also Counts the number of cells that are not empty and That wasn't helpful as I have already been to the help index. [quoted text clipped - 5 lines] 1 1 1 1 1 1 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200802/1 |
My Dynamic Named Range isn't working
Then it's not dynamic, right?
No, it's dynamic based on: Height = COUNTA(Dates!$E$3:$E$65536) Width = COUNTA(Dates!$E$3:$IV$3) -- Biff Microsoft Excel MVP "Carrie_Loos via OfficeKB.com" <u34134@uwe wrote in message news:802011fc3c66f@uwe... Then it's not dynamic, right? T. Valko wrote: 46 Row Tim Smith Index 1 1 1 1 1 1 I have no idea what that's supposed to represent.... Try this: =OFFSET(Dates!$E$3,0,0,COUNTA(Dates!$E$3:$E$6553 6),COUNTA(Dates!$E$3:$IV$3)) That wasn't helpful as I have already been to the help index. [quoted text clipped - 5 lines] 1 1 1 1 1 1 -- Message posted via http://www.officekb.com |
My Dynamic Named Range isn't working
Thanks Don - You gave me an idea that ended up working well. I tried the
match but it made the range too big and I need to name the range in little pieces so I can place a Vlookup formula in the range and cut down on the run time. Bottom line, I see that my 4 columns to the left and top two rows are causing my issue. Therefore, I placed negative numbers at the end of the CountA formula and it worked famously. It will be interesting to see how it behaves once I write it into the vb. Hopefully it's not disasterous =OFFSET(Dates!$E$3,0,0,COUNTA(Dates!$E:$E)-2,COUNTA(Dates!$3:$3)-4) Don Guillett wrote: instead of counta( etc use match(9999999,$3:$3)-5 and check by touching the f5 key and typing in the name of the defined range if text use "zzzzzzzzzz" Yes, I see, thanks - Is there any other solution to a dynamic range? Since the range is picking up 3 extra columns can I put another offset -3 [quoted text clipped - 10 lines] 1 1 1 1 1 1 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200802/1 |
My Dynamic Named Range isn't working
Glad to help
-- Don Guillett Microsoft MVP Excel SalesAid Software "Carrie_Loos via OfficeKB.com" <u34134@uwe wrote in message news:80214b0c8ee53@uwe... Thanks Don - You gave me an idea that ended up working well. I tried the match but it made the range too big and I need to name the range in little pieces so I can place a Vlookup formula in the range and cut down on the run time. Bottom line, I see that my 4 columns to the left and top two rows are causing my issue. Therefore, I placed negative numbers at the end of the CountA formula and it worked famously. It will be interesting to see how it behaves once I write it into the vb. Hopefully it's not disasterous =OFFSET(Dates!$E$3,0,0,COUNTA(Dates!$E:$E)-2,COUNTA(Dates!$3:$3)-4) Don Guillett wrote: instead of counta( etc use match(9999999,$3:$3)-5 and check by touching the f5 key and typing in the name of the defined range if text use "zzzzzzzzzz" Yes, I see, thanks - Is there any other solution to a dynamic range? Since the range is picking up 3 extra columns can I put another offset -3 [quoted text clipped - 10 lines] 1 1 1 1 1 1 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200802/1 |
| All times are GMT +1. The time now is 01:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com