Solved Keep cell locked to specific cell range when inserting cells

February 10, 2016 at 23:08:56
Specs: Windows 7
The way I have set the work sheet I am trying to keep (D4) always refer to (O4:AG) (works fine with $) but after cells are inserted to (M4) the reference moves with it.
From all I've read I'm still not sure which formula to use.
I've tried to in both Excel 2010 & Open Office Calc 4.1

See More: Keep cell locked to specific cell range when inserting cells

Report •


#1
February 11, 2016 at 08:11:10
I'm confused by your question in a couple of manners.

First, you say that D4 refers to O4:AG. Do you mean O4:AG4? There should be a Row number after AG.

Second, I don't see why you want to lock the reference to O4:AGx if you are inserting cells. If you insert cells between Column D and Column O, Column O will move, as will all the data that is in it and any column afterwards.

If the formula in D4 doesn't change when the columns move, then the formula will no longer refer to the same data.

Please explain what you are trying to do in a little more detail.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
February 11, 2016 at 10:02:30
Please excuse my typo, yes I do mean O4:AG4 and yes it does move when cells are inserted which I Don't want. I'm inserting cells that move everything to the right with old references going into archives (may still need to verify history) to allow the newly input to be referred to but not move the end point.
In the reads somehow one of the formulas allow reference to always start 10 cells to the right and end 20 cells to the right even after cells are inserted.
Thank you,
BC

message edited by Basecalc


Report •

#3
February 11, 2016 at 13:27:05
✔ Best Answer
How about trying the OFFSET function:?

OFFSET(reference, rows, cols, [height], [width])

e.g. =SUM(OFFSET(D4,0,11,,19))

This should always SUM O4:AG4, assuming I counted correctly ;-)

(The double comma simply means that I didn't want to include a [height] argument.)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
February 11, 2016 at 23:14:20
Thank you again DerbyDad03.
After reorganizing my spreadsheet the formula appears to work. The real test will reveal it's intent over the weekend and then I'll submit a follow-up
I really do appreciate the help as there are few people as helpful as you!
I'm sure I'll have a few more questions in the future and it's nice to know a great place to ask them here @ Computing.Net,
BaseCalc

message edited by Basecalc


Report •

#5
February 12, 2016 at 19:42:48
Well I had time to work with the spreadsheet and (as I remember from some reads) that the tailing's that get moved to the right after insert cells for some reason it continues to calculate (holds on to) the original end reference though the same starting reference remains correct unless that column of tailing's are deleted.
Same effect OO Calc 4.1.0 & Excel 2010, UHG.
Also I tried nesting the suggested formula to eliminate columns that need to be ROUNDDOWN, & AVERAGE, to mention some, that produce an incorrect result but will work on that later.
Still, thank you.

Report •

#6
February 13, 2016 at 20:20:29
I'm not sure what you mean and I can't tell if my formula worked for you or not.

Perhaps if you shared exactly what you are trying to do, and included some example data, we might be able to be of more "direct" help.

If you are going to post example data, please click on the following line and read the instructions found via that link.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
February 13, 2016 at 21:48:04
If I knew how to post with an image it would most likely help you to understand so I'll try again.
The formula in Calc is equal sum open paren offset open paren D5 semicolon 0 semicolon 14 semicolon semicolon 18 close paren close paren
I have statistics in columns A5:K5, L5 is where the new values are inserted (right click,Insert/shift cells right) but only use R5:V5 for the stats and from M5:V5 are the numbers that are stored for statistics.
Your formula Does sum correctly from R5:V5 but after I insert in L5 and shift right (moving V5 to W5) it then expands continually with inserting to calculate R5:W5 (or more unless I delete column W imediately) which my intention is to keep everything shifting right to what I call Archives. I've tested it with both Calc & Excel with same results. For some reason the end point continues to expand as does the number in D5, not good.
So the answer is no, it doesn't work for me as if I have to go through that correction I'd rather just set an ABSOLUTE range and re-adjust the numerous rows 1 by 1 after each insert rather than re-adjusting an offset formula or cut & paste W5 to another location. There are too many names in to reconfigure to have names in columns and shift info down and would most likely have the same result.
Hope this helps to understand.

message edited by Basecalc


Report •

#8
February 14, 2016 at 19:22:37
First, I'm not quite sure why you typed out the formula in words instead of just pasting in the actual formula. It would have been easier for you and easier for us.

Second, I pointed out the link to the instructions on how to post example data in this forum. You don't need to post an image, you can post actual data.

Third, you've confused me again. You originally said that you wanted to reference O:AG. I therefore suggested an OFFSET function with a cols argument of 11 and a width argument of 19. This formula will always SUM O:AG even if you insert cells before Column O.

Now it sounds like you only want to SUM R:V. If that is the case, why did you use a width argument of 18? The width argument determines how wide the referenced range should be, starting with the column determined by the cols argument. i.e. how many columns to SUM.

If you only want to SUM 5 columns, then you should be using a width argument of 5. If you use 18, the formula will SUM 18 columns, starting with R. Your formula will always SUM R:AI.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
February 14, 2016 at 19:44:42
Sorry for the confusion and thank you for the effort.
A picture is worth a thousand words for those of us that are visual.
Sum formula even if $ moves with insert cell
Offset doesn't move the beginning ref but expands the end ref when insert cell
Neither work for me
BC

Report •

#10
February 14, 2016 at 20:20:56
I don't have access to Excel at the moment, so I can't test anything, but I will work on it tomorrow.

If need be, you can post a copy of your workbook at zippyshare (or other file sharing site) and then post the link back here. That way we can see firsthand what the issue is. Just don't choose a share site that asks us to register, because we (well, at least I) won't.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#11
February 15, 2016 at 15:07:17
I have tested the SUM(OFFSET) formula that I suggested numerous times and it does not "expands the end ref when insert cell" as you say it does.

I have uploaded an Excel workbook to zippyshare.com. This file contains some data and the following formula:

=SUM(OFFSET(D5,0,14,,5))

This formula will SUM(R5:V5)

The file also includes 2 simple instructions involving the insertion of a column between D & R to prove that the formula will always SUM(R5:V5). It will not include any column past V. It will not expand it's end reference when a column is inserted.

Here is the link to the file.

http://www26.zippyshare.com/v/gSeq1...


message edited by DerbyDad03


Report •

#12
February 16, 2016 at 12:27:06
Hello DerbyDad03,
After giving thought to what I have created and read, I'm leaning in the direction that I created a worksheet that is not organized to the specifications that spreadsheets are designed, meaning, that there most likely are Too many references by other cells (5 or 6) to the specific range causing my error. I am using Open Office Calc 4.1.0 in reference to this spreadsheet, though I do use Excel 2010 for some of it's features and to verify the results are the same. Because I am trying to help another with this I don't have the authority to divulge their information.
So on that note I'll do some research for reconstructing a proper worksheet and when completed will then verify my belief of references.
I do want to thank you and all others for your help. Believe me, I'll be back if at least to find the proper method.
Again, Thank You,
BaseCalc

Report •

#13
February 16, 2016 at 18:13:30
I have created a new file and yes, as I expected, the formula is correct as you mentioned. Of course now there's a similar problem with another factor while Insert, right click, move cells right as it does keep the formula calculating correctly though I need to figure out how to keep the formula used for that cell to stay in that cell that gets inserted from.
Here's a link to a Zippy Share Sample:
http://www120.zippyshare.com/v/YyRp...
In the meantime I'll be searching a solution
BC

Report •

#14
February 16, 2016 at 18:58:43
re: "I need to figure out how to keep the formula used for that cell to stay in that cell that gets inserted from."

Unless you use a macro, that's not going to happen "automatically". You can't insert a cell and expect Excel (or Open Office) to retain the info from the cell that used to be in that location. That's not the point of inserting a cell.

As long you are manually inserting a cell with the mouse, add one more step and drag the formula back.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question