I would like to lock reference to cells (not the value in the cell) and prevent that from changing regardless if I insert, delete, or move the column/row/value.

Example (see attached spreadsheet)
B3 calculates the sum of the last four months of sales, C3:F3 (Jan - April). Next month I will enter May's sales and want B3 to still sum up the last four months (so Jan will not be part of the calculation but the new May will).

At this point, when I insert a new column before C to post the May sales data, B3 changes reference to D3:G3.



Is there a way to lock the reference to C3:F3 regardless of what happens to those cells?

Thanks.

Attached Files

a.xls (64.0 KB, 9 views)

I think the OFFSET function will do it for you. Yep, that's what you want. Try this:The OFFSET function starts with a given cell (B3, in this case, which is the very cell the function is in), then adds a certain number of rows and columns to it. I specified 0 rows and between 1 and 4 columns.

You can just as easily specify a different starting cell and then correspondingly different row and column offsets; this just seemed intuitively obvious to me.

An alternative

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Ah, of course. I used to use INDIRECT a lot, and then decided that for many situations I should use OFFSET instead. But for this case I think Peco's solution is better: It's more intuitively obvious to the eye.

In fact OFFSET and INDIRECT being voltaile functions, I don't like to use them.
In this case the use of INDEX might be better but I haven't got XL on my Linux machine to test it

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Thanks guys. It appears the INDIRECT function is best suited for this. Most of my reference are nested if statements, thus the INDEX function just makes the formula longer and more confusing.

Wait—OFFSET and INDIRECT are volatile? Why would that be? Well, I guess I can see why INDIRECT would be, though I never thought about it before. But OFFSET shouldn't have to be volatile. How do you know? Have you read it somewhere, or what?

Bob, perhaps have a look at this page

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Now that is good information! Thanks, Peco. I think I want to bookmark it, but I don't have a handy category for it. I'll figure something out.