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

Offset and dynamic columns and rows

G

GN0001

Member
How can I use the offset formula with dynamic formulas for rows and columns?

I have offset(L2,0,3)

The next will be (L2,0,5)

what formula can I place instead of 3 and 5?

Many thanks,

Guity
 
Like a dynamic range?


Typically you'd use =offset(l2,0,0,3,1). You would replace the 3 with a count or counta in many circumstances.
 
What is the relationship of the cell with the formula to the Array of data and what changes the 3 to 5?
 
When you say the next will be =(L2,0,5)

Next Row or Column ?


seeing you didn't say where this was going its a bit hard but in general


Next Row =($L$2,0,(Row()-Row(cell))*2)

Next Column =($L$2,0,(Column()-Column(cell))*2)

where cell will be the adress of a cell such that

Row()-Row(cell))*2 = 3

Cell will be $A$1 format
 
Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and the number of columns to be returned.


what does above means in easy language !!
 
Offset allows you to reference another location from a fixed point using variables to define the new location and size of the new location.


eg:


=Offset(a1,5,2)

will return the value from C6, A+2=C, 1+5=6


=Sum(Offset(A1,5,2,7,3))

will sum the area from C6:E12

That is an area 7 Rows High, 5 Columns wide

with Top Left corner at D6


The advantage is that we can use cells references instead of fixed num,bers to define the location, size and extent of the modified range.


This leads to connection to controls for moving reports/charts and the use in Dynamic Ranges or Ranges that expand as data is added to them.


eg: Put this somewhere

=Sum(offset(A1,0,0,counta(a:a),1)

or

=Sum(offset(A1,,,counta(a:a),1)

now start entering data in Column A


You now have a range that is expanding as you add more data to the column.
 
Suggest you read the following


http://chandoo.org/wp/2008/11/19/vlookup-match-and-offset-explained-in-plain-english-spreadcheats/
 
I have a range like this:

A B C D

1 Selected Unselected New Old

2 BB DD 5 7


I use offset to get the value in Cell A2:

=OffSet(A2,0,0) that gives BB

I want to move in the row, no height

If I want to get the value in Cell B2, the offset formula would be:

=Offset(A2,0, 1) that gives DD

Now I don't want to use 0 and 1 to move across the row, I want to use a formula instead 1 and 2. what would be the formula?

Kindly
 
To get BB you just say =A2, no need for offset, and if you copy this formula cross all columns you will get the data you want without the use of offset.
 
You must log in or register to reply here.
Back
Top