1

I am having an issue with autofill in Excel 2016.

Excel autofill issue

When I use autofill, a reference in the first/initial formula changes unexpectedly (see "Excel autofill issue" gif link above). The reference to "AA4" should remain in the first formula and should autofill to "AA5" in row 5, "AA6" in row 6, etc.

Calculation setting is on automatic.

For the life of me, I don't understand why Excel changes the "AA4" reference in the first row. Is this expected behavior or is this a bug?

karel
  • 13,390
  • 26
  • 45
  • 52
Jos
  • 11
  • 3
  • This does seem like a bug - I've never seen it alter the cell it's being dragged from.Try copying cell AD4 and pasting to AD5:AD9 – Smock Feb 04 '20 at 14:37
  • Thanks for your response! Same here, I have never seen Excel do this. Any suggestion on how to potentially solve or work-around this? – Jos Feb 04 '20 at 14:41
  • Try copying cell AD4 and pasting to AD5:AD9 (via right click or ctrl-c + ctrl-v) – Smock Feb 04 '20 at 14:43
  • I've tried copying AD4 to AD5:AD9 as you suggested, that work-around indeed works just fine. Doesn't solve the issue though... – Jos Feb 04 '20 at 14:54
  • Yeah - it is very interesting though, that C+P works but dragging doesn't. At least it's a work-around. Would you be able to post the file somewhere? Just wondering if it would replicate if others tried your particular file. – Smock Feb 04 '20 at 15:00
  • Sure. Here is a cleaned-up version. Now it concerns column G: https://drive.google.com/file/d/1Id6n6hLje96H7jPlIFn_qlNFVh3g_OlR/view?usp=sharing . The weird thing is, once I delete the autofilled lines that were added, the initial formula reference goes back to what it was before autofilling. – Jos Feb 04 '20 at 15:27
  • I think it's something to do with the fact you're using a table, and referencing cells directly rather than by column name - I get a warning message when I try to drag-copy downwards: ***This table inserted rows into your worksheet. This may cause data in cells below the table to shift down***. Quite what this actually means, and why it updates the original cell I can't say though. I can replicate though! :( Hope that helps someone else figure it out though – Smock Feb 04 '20 at 15:48
  • Thank you Smock for your effort, that is well appreciated. Anyone else willing to weigh in on this? Could the solution potentially lie in re-writing the formula without direct cell referencing within the table (not aware this is possible for this particular use-case)? Or does someone have another suggestion? – Jos Feb 04 '20 at 15:59
  • @Jos,, I'm unable to find complete set of source data,, so better upload the file of cloud or Dropbox!! – Rajesh Sinha Feb 05 '20 at 06:21
  • @Rajesh: it's on Google Drive, see the link in the comment above. Can you open it? – Jos Feb 05 '20 at 08:19
  • Does [this](https://www.quora.com/What-does-mean-in-an-Excel-formula?share=1) gives some insight to the problem? – p._phidot_ Feb 05 '20 at 19:33
  • @p._phidot: thanks for your suggestion, greatly appreciated! Can you or anyone else help me to change $AA$4:AA4 to a [structured reference]? Not sure how to do that... – Jos Feb 07 '20 at 10:19
  • since every other evaluation will gives "" , `=IFERROR(IF(COUNTIF($F$4:F4,F4)=1,F4,""),"")` should be enough. [ sorry, I'm not familiar with table reference + had tried my luck using it.. no luck that way for me.. ] – p._phidot_ Feb 10 '20 at 07:08

0 Answers0