Offset Formula Changing Row References

[suanne]

I am currently working on a spreadsheet that uses an Offset formula to reference data from another tab in the workbook. When I copy the formula down, the formula stays exactly the same, but I need the row reference (8) to change to (9) and so forth down the spreadsheet. Below is an example of the formula I am using.

=OFFSET(PPSS,8,$CW$1+B$1)

*Note: When I copy across columns to the right, the column reference changes, so not sure why the row reference won't change when copying down. I don't want to have to go in and manually change all the row references.

Has anyone else faced this dilemma? If so, any suggestions?

 

[gecs]

First please explain how do you want the formula to change when copied. Till now I understood that you want the rows argument (the 8) to follow the row number of the cell where the formula resides, but what about the third argument ($CW$1+B$1)? How do you expect this to behave when copied? Do you want to copy the formula both vertically and horizontally? In this case what should be the behaviour of the third argument?

 

[suanne]

Thanks so much for your reply.

I want the column argument ($CW$1+B$1) to remain the same regardless of where it is copied. Since there isn't an absolute before the B, I expect that to change to C$1,D$1, and so on, which it does. Also, I expect the row reference (8) to stay the same when copied to the right. I only want the 8 to change to 9 when copied down to the next row.

Does this make sense?

=OFFSET(PPSS,8,$CW$1+B$1)

Thanks again for any help you are able to provide

 

[gecs]

=OFFSET(PPSS,ROW($A8),$CW$1+B$1)
In the above formula the the third argument will return 8, so you need to write it like this in the row where you need this argument to be 8. If you copy it up or down, the second argument will be 7,6... or 8,9...

 

[suanne]

It worked!! Thanks, GECS! You are a life-saver! This will save me so much time.

 

[gecs]

You're welcome.