absolute cell references (eg $a$n) copy as expected but not as required

Win XP Pro SP3

Excel 2010

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?

-------------Problems Reply------------

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.

Category:Office Views:0 Time:2012-04-15

Related post

  • How do I change an absolute cell reference to a relative cell reference? 2012-09-13

    The help menu tells me that relative is the default, but for some reason this is not happening. Here is what I have been doing: Copy cell in source workbook In destination workbook - Paste>Paste Special>(Paste>All) (Operation>None)>Pas

  • Absolute cell reference that changes after every fixed number of rows down... 2012-09-21

    I'm trying to use an absolute cell reference in one column and use it in a formula in the adjacent column to the right. I want to copy this simple formula all the way down column B, BUT the absolute reference needs to change every sixth row down. If

  • Linking data (single cell or row) to Word 2010 uses absolute cell reference 2013-04-05

    I have an excel spreadsheet where I want to link only a single cell (A10) to a Word 2010 document. However when a insert a row above A10, it updates Word and shows the data that is in A10, instead of A11. The same happens if I sort the excel document

  • f4 key for absolute cell references 2012-07-01

    In excel, I need to use the F4 key to use a cell reference in a formula to refer to a specific cell (Absolute Cell Reference = $B$16). When I click on F4, a box comes up on my screen with 4 options to click (Computer only, Duplicate, Extend, and Proj

  • Absolute cell reference F4 doesn't work in Excel 2010 2013-12-15

    Hello - does anyone know what is the new Absolute Cell Reference shortcut in Excel 2010, Windows 7? I used to use F4 in my last version of Excel but this no longer seems to work. Thanks --------------Solutions------------- Hello - does anyone know wh

  • Excel 2007 F4 key does not toggle relative/absolute cell references. 2014-03-26

    F4 key does not toggle relative/absolute cell references. My F4 (function Key 4) was working well and suddenly stop functioning. I think it may be the Flash Cards but I do not want to do anything because probably I can make it worse. Is there somebod

  • Non-absolute cell references not changing when sorting 2014-01-10

    I have a column with a formula that references data within the spreadsheet and also data on a different tab of the same file. The formula has absolute references for the data on the other tab. The issue is that after I sort, on the first part of the

  • Absolute Cell Reference 2014-02-16

    I have a markup value In Cell A1 Office Excel 2013 When referencing the Cell on it's own for eaxample =($A$1) it works But when doing a calculation such as =($A$1*2) It produce @VALUE! Any help on this would be appreciated. --------------Solutions---

  • Using F4 to change absolute cell reference does not work 2015-02-11

    Hi to all, Does someone have an idea how it comes that pressing F4 does no longer allow to put the dollar sign $ in my formulas to switch the cell reference style? Thx for your hints --------------Solutions------------- Hi, Try tapping F1, does help

  • update multiple worksheets from a module with an absolute cell reference? 2011-11-18

    I'm in a module function, and I have a value that needs to get updated across multiple worksheets. I would like to take a data driven approach to this, since it may change a bit in the future. In essence, I want to create an array of strings, each en

  • How do is use conditional formatting with an IF then formula with column absolute cell reference? 2015-01-10

    Hello everyone, new to the community here, I have a data sheet with a lot of random text in each cell, with each column relating to a question and the responses in each column. How do I use conditional formatting to highlight cells that include text

  • copying different formulae in rows or columns without changing the cell reference 2012-03-05

    I have simlar looking formulae in an Excel table, in either rows or columns but some attribute e.g., criteria for evaluation, is different in each of the cells. My problem is that I want to copy the set of such formulae in a series of cells either in

  • Cell references does not change when sorting 2014-07-09

    Hi, i have seen several queries related to sorting, but not entirely sure if the problem is the same as mine so hopefully someone can help shed some light on whats going wrong for me (i'll be as detailed as i can): I have an excel file with a number

  • Random cell reference inserted into text strings 2012-06-18

    I manage a spreadsheet that contains large blocks of text. (Our engineering group uses this spreadsheet to handle text strings that are used by our code base.) Every once in a while, I'll be reviewing the spreadsheet, and there will be a random absol

  • Create links with Relative cell references by default. 2012-08-18

    Working in MS Office 2007 the normal function of Excel when creating link to anther workbook (another file) is to return in the destination a link with absolute cell references for example with both files open linking between the two will result in a

  • i am unable to use f4 as a absolute cell in my exercises 2013-11-21

    I am using office 2013 and unable to use f4 in excel for absolute cell reference --------------Solutions------------- I suspect this is the behaviour of your computer or laptop's keyboard. Many computer and most laptop keyboards use the function keys

  • Disable Excel's Automatic Cell Reference Change After Copy/Paste 2011-07-12

    I've got a massive Excel 2003 spreadsheet I'm working on. There are a lot of very large formulas with a lot of cell references. Here's a simple example. ='Sheet'!AC69+'Sheet'!AC52+'Sheet'!AC53)*$D$3+'Sheet'!AC49 Most of them are more complicated than

  • Relative cell references act like absolute references upon "drag-n-drop" of formula containing cells 2014-04-28

    Hello, I've created a large sheet for monetary budgeting over the past 2 years, adding to it week by week. Just in the past day have I experienced a very strange problem. When I re-arrange parts of the spreadsheet by dragging and dropping groups of c

  • Copying formulae with cell references 2014-09-16

    In Excel 2003 how do I copy a formula that refers to other cells so that it still refers to the same cells For example in cell F1 I have a formula that refers to cell G1, I want to copy and paste the formula to cell F2 And still have it refer to cell

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.090 (s). 11 q(s)