• 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.

Need clarification OFFSET Function

vijay.vizzu

vijay.vizzu

Member
Dear All Excel users,


I am going to create a dynamic range by offset function in my sheet. but while defining a range, i observed that, when i reopen the file, the range in offset will change automatically. I am just confused, but finally i realised that i didn't put range as an absolute reference, then i put $ in the range to make it as absolute reference. Now i want to know that in my formula [=OFFSET($P$1,0,0,COUNTA(P:p),1)]i am putting offset on P column and for dynamic i am using COUNTA function to adjust range itself. but after reopen the file the offset formula will look like this [=OFFSET($P$1,0,0,COUNTA(Q:Q),1)] i get confused and put it $ in the range like =OFFSET($P$1,0,0,COUNTA($P:$P),1) now it works fine. Can you tell me why this happend means range changes automatically if we didn't use $.


I have created three dynamic ranges like

=OFFSET('04X-SOUTH'!$P$10,0,0,COUNTA('04X-SOUTH'!$P:$P),1) Range name as Need.

=OFFSET('04X-SOUTH'!$Q$10,0,0,COUNTA('04X-SOUTH'!$Q:$Q),1) Range name as PIC.

=OFFSET('04X-SOUTH'!$R$10,0,0,COUNTA('04X-SOUTH'!$R:$R),1) Range name as Vendor.


Above two formulas working fine and adjust the ranges itself if any data entered, but third one taking one cell (Last cell) extra in the range. Can you clarify why this is happening.


Thanks

Vijay
 
Hi, vijay.vizzu!

Is actually empty last cell in column R? Or just display blank?

Regards!
 
Hi Vijay ,


The purpose of using OFFSET and COUNTA is to make a named range dynamic , so that it expands as fresh data is added.


However , please remember that using different columns in the COUNTA function can cause problems without you being aware.


As far as possible , when using the COUNTA function , use a base / reference column , which will strictly define the extent of data within your worksheet ; this column should not contain blanks , anywhere in between or even at the beginning or the end. Once this column has a blank cell , it should mean that there is no data in this blank cell and beyond.


Once you have such a column , use this same column , with the absolute sign ( $ ) within the COUNTA function for all your ranges. If any other column has blanks anywhere within , using the base / reference column even for that column , will ensure that blanks are returned wherever they are present. Also all named ranges will have the same number of rows , which is absolutely essential in array functions like SUMPRODUCT.


Regarding your other question as to the necessity for the $ signs in the column references ( P:p or Q:Q ) , you can try this out - see where your cursor is when you create the named range ; if your cursor is in column P , and you do not use the $ sign , then Excel takes the reference as a relative reference , relative to the position of the cursor ( active cell ). Close the Name Manager , move the cursor to any other column , say column T , and reopen name Manager , and check the refers to box for the named range ; you will see that what was earlier P:p has now become T:T.


The $ sign tells Excel to take the references as absolute references , and not relative to the active cell.


Narayan
 
Dear Narayan,


Thank you for your information. i will keep it in my mind while defining dynamic range


Dear SirJB7,


The last cell doesn't contain any contents i have checked properly.


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