Win XP Pro SP3
I don't know if $a$n is actually called an absolute cell reference. I am sure someone with put me right...
I need to copy and paste sets of calculated cells with absolute cell references, along with the referenced cells so that the copy of the absolute cell references refer to the copied referenced cells, not the original referenced cells.
On a worksheet there is a group of cells used to produce costs for the work I do. within the group of cells there are some allocated to typical costing parameters for example commission rate, and some of the calculated cells in the group refer to those parameters with absolute references. When a row is copied and inserted, the new calculated cells alway refer to the same costing parameters which is fine for that group. I want to select the group and copy it further down the worksheet multiple times. Each group has its own set of parameters because the entire group is copied. However, that seems irrelevant to the Excel copy procedure because I have found that every copied group still refers to the parameters in the first group. And I suppose it should on considering what $a$n means.
So how to I do this copy, or how do I arrange the calculated cells so that each copied group refers to its own set of parameters?
You actually don't want to use absolute cell references in your formulas.
Once you have your formulas set up correctly and copied as needed, then the $s can be removed. Select your cells, press Ctrl-H, enter $ as the Find what and leave the replace with blank.
Then you can copy the cells and paste as needed.
Thanks for the suggestion but I am loath to perform mass editing of calculated fields containing financial data.
The conundrum is that to copy the groups, there mustn't be any $-references, but to insert records within each group, the $-references must be present.
I think this is a fairly 'normal' type of requirement but seems to have been missed within Excel. It looks as though I will have to write an Add Record script to create the effect of $-references unless there is another solution out there...
Sure, it is done all the time, but the usual routine is to copy the sheet and use the copy as the new table.
But, if you want to do what you describe, create a copy of your sheet, then cut the table and all referenced cells from the copy of your sheet, and paste them onto your original sheet wherever you want them. Finally, delete the copy of the sheet.
I have just tried this I believe as you have suggested, and the pasted group of records continue to refer to the absolute reference in the first group of records on that worksheet.
This is a case where it appears that a switch of some sort is required to turn off this behaviour in some controlled manner.
You need to move ALL the used cells - including those used in the formulas as source cells.
I believe I have done all you suggested. You are welcome to try yourself.
I refer to the 'table' in the template as a 'group' of cells because I feel that is a more generic term. Although I trust you will understand what I mean by 'table' when you see the workbook.
There is one absolute reference to a cell outside the group of cells. I am not worried what happens to its references; and other absolute references to two other cells within the group. They are the focus of this issue.
The template for the workbook is here if you are interested. I just did a quick trawl to find somewhere to dump it. Please do you virus checking. I'm sure it left my PC ok but I can't guarantee the FileDropper site.
I like SkyDrive - here's what I got when I followed the instructions that I posted:
Which cell is it not working for?
Yes i see now. Thank you. I don't think I cut before, just copied. Copying enforces relative references.
I wonder why cut doesn't? Or maybe it's a bug? Maybe cut should enforce the copying of absolute cell references like copy but it wasn't implemented properly? Or do you know differently?
Cut does what it is supposed to - remove the cells from the structure of the worksheet and plops them down on another, working as they did before the cut. Cut is different from Copy, which is why I told you to use it.