0

I have a simple spreadsheet with just one formula, which auto-increments the number in the first column, going from cell A6 down. So the value in A6 is hard-coded 1, then A7 contains the formula =A6+1. I then autofilled the rest of the first column with that formula, and sure enough each cell in column A has the formula, and the displayed numbers are 1, 2, 3, etc.

But if I select a row and move it up or down by Shift-dragging, the formula in cell A disappears and is replaced with the hard-coded former contents. WHY? I can find nothing in the preferences to explain this major dysfunction.

This is Excel 2011 on Mac.

Thanks for any insight!

Update: So I guess Excel is just fundamentally broken, at least on the Mac.

  • 1
    Shift-dragging? What do you mean by this? Can you be more specific? – Scott Holtzman Nov 25 '15 at 3:34
  • 1
    Use =ROW()-5 instead. – Kyle Nov 25 '15 at 15:50
  • 1
    Shift-dragging means to hold the Shift key down while dragging. It appears to be the only way to move a row in Excel, since "cutting" doesn't cut at all; the row remains in place, even after you "paste" it elsewhere. – Oscar Goldman Nov 26 '15 at 8:40
  • Thanks, Kyle. That may work in this case, but it still doesn't answer the question of why Excel isn't auto-adjusting the formula as it's supposed to. – Oscar Goldman Nov 26 '15 at 8:56
  • 1. I'm not familiar with 'shift-dragging'. It does nothing for me but I'm currently on 2007 so maybe it was introduced later. 2. I don't believe this is true: "the formula in cell A disappears and is replaced with the hard-coded former contents". 3. I don't believe this is true: "since "cutting" doesn't cut at all; the row remains in place, even after you "paste" it elsewhere". Perhaps if you were to post a more detailed description of what actually happens (including screenshots?) we could help work it out. – Alex M Mar 15 at 0:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.