Using OFFSET

[dmcnab]

Hi all....I want to use OFFSET (?) to get the values from B1:B9 on Sheet1and have them show up on Sheet2 in cells F1, F3, F5, F7, F9, F11, F13, F15, F17........any suggestions? Thanks.

 

[Hui]

In Sheet2 F1 put

=OFFSET(Sheet1!$B$1,INT(ROW()/2),)

Copy to F3, F5, F7 etc

 

[dmcnab]

Hi Hui...I made a mistake in my 1st post and I can't believe that I did this, but what I wanted was to get values from B1:B9 on Sheet 1 and have them on Sheet 2 in F1, H1, J1, L1 etc etc....

 

[dmcnab]

http://www.speedyshare.com/files/28334675/offsetting_using_a_helper_row_not_working.xls


password is xifonedebeve


Hui...I have uploaded a small sample file showing what I am trying to do, b/c I want the formula to skip every 2dn column (see sample)....hope this makes it clearer? Thank you.

 

[Hui]

=OFFSET(Sheet1!$B$1,INT(COLUMN()/2)-3,)

 

[dmcnab]

Hi Hui....this works the way I want it to, but how does it work? I can't figure out how it works and I like to understand that....what does INT do and what does column()/2 do? And what does the -3 do? Thanks

 

[Hui]

Column()/2 takes the current column and divides it by 2

Int takes the integer part of the above number

-3 takes off 3 from the above

Using F1 as an example

F is column 6

6/2 = 3

int (3) = 3

3 - 3 = 0

so F1 offsets 0


Using J1 as an example

J is column 10

10/2 = 5

int (5) = 5

5 - 3 = 2

so J1 offsets 2

 

[dmcnab]

OK...I think I get it...and if you had data in very cell from B1:g1 and wanted to copy it into every cell from F6:F11, would you change Column to Row?

 

[Hui]

Yes, Except that to offset across you will use the Column offset not the Row offset within the Offset Function

=OFFSET(Sheet1!$B$1,,ROW()-6)

Note the blank Row offset ,,

 

[niting]

Hi forum,


The qstn related to the topic, dats y posting here. I beieve dat offset can be used to change dynamically the sum range, i.e. if month wise values r dere till june, dan wen july values r inserted, d offset automatically considers it and sums it. I tried lookin fr a solution, but did not find, so i devised one myself, which worked but has lots of arguements. d formula i used is-:


SUM(OFFSET($F$3,,,COUNTA($F$3:OFFSET(F11,-1,))))


where F11 is the cell where i m summing the values before it, which changes as i insert rows. Is this the right way to solve fr d problem, irrespective of d fact dat i got d corrct answer.


Thanks

Niting

 

[Hui]

Niting


What you have done is ok

But will fail if someone adds a row after F11


Generally when doing these you will ensure that nothing is stored below your data area and then use a formula

=SUM(OFFSET(F3,,,COUNTA(F:F)))

or if you have headers above F3

=SUM(OFFSET(F3,,,COUNTA(F:F)-COUNTA(F1:F2)))