• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Problem in Defining Dynamic Range in Excel 2007

vijay.vizzu

vijay.vizzu

Member
Dear all,


I can't able to understand why this problem occurs. I am going to create a dynamic range which will adjust the range and count Blank cells in that range. Currently my data is from G3:G921 which contains some blank and non blank cells. Now i created dynamic range with formula =OFFSET(RFQ_Des_New!$G$2,0,0,COUNTA(RFQ_Des_New!$G:$G),1). But it selects only from G2:G753.It can't select whole range. Please clarify where i done mistake.


thanks

Vijay
 
Hi Vijay ,


The COUNTA function counts the number of cells that are not blank ; so if your range is filled with data in every cell from say G7:G36 i.e. 30 cells , the COUNTA function will return 30.


Now , if you use the OFFSET function with a reference of G7 , and a height of 30 rows , you will be referencing the range G7:G36.


If , however , you have blanks in cell G27 and G29 , then COUNTA will return 28 instead of 30.


Now , your OFFSET will reference a range G7:G34 , since the height or number of rows is only 28. Your last data cell may be G36 , but with the two blanks in between the starting cell of G7 and the end cell of G36 , you will access the range G7:G34.


Use this formula to address your named range correctly ; I am assuming your data is numeric.


=RFQ_Des_New!$G$2:INDEX(RFQ_Des_New!$G:$G,MATCH(9.99E+306,RFQ_Des_New!$G:$G,1))


If your data is text , then replace the above MATCH statement with the following :


MATCH("*",RFQ_Des_New!$G:$G,-1)


If you want a formula which will take care of all alphanumeric data , then the following can be used :

[pre]
Code: 
=RFQ_Des_New!$G$2:INDEX(RFQ_Des_New!$G:$G,MAX(IFERROR(MATCH(9.999999E+306,RFQ_Des_New!$G:$G,1),0),IFERROR(MATCH("*",RFQ_Des_New!$G:$G,-1),0)))
[/pre]
Narayan
 
Thank you ,Narayan


But by your formula =RFQ_Des_New!$G$2:INDEX(RFQ_Des_New!$G:$G,MAX(IFERROR(MATCH(9.999999E+306,RFQ_Des_New!$G:$G,1), it only selects first cell (G2) & Last cell in the range (G921), if i am putting offset before your formula then it shows error


Vijay
 
Hi Vijay ,


The formula posted is the complete formula ! OFFSET is not required.


I have avoided OFFSET because it is a volatile function.


Narayan
 
Dear Narayan


thank you so much. but can you explain about MATCH(9.999999E+306, i know that, for larget number you have used 9.999999E but why you have added 306. Please tell me


Vijay
 
You must log in or register to reply here.
Back
Top