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

I'm confused by your question in a couple of manners. First, you say that D4 refers to O4:AG. Do you mean O4:AG

4? 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.

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

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.

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

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.

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.

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

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

colsargument of 11 and awidthargument 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

widthargument of 18? Thewidthargument determines howwidethe referenced range should be, starting with the column determined by thecolsargument. i.e. how many columns to SUM.If you only want to SUM 5 columns, then you should be using a

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

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

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.

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

alwaysSUM(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

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

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

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.

Ask Your Question

Weekly Poll

Do you think Salesforce should have bought Slack?

Discuss in The Lounge

Poll History