1

Hopefully this makes sense.

In Excel, I have a table with 12 columns. Column A is a file number hyperlink that takes you directly to the specified job folder on our server. When I insert new rows into this table, no matter how many rows I insert, the first cell (column A) of each new row always auto-fills with the same file number hyperlink for an old job. It's not random or anything, it's always the same one. I've never had any luck figuring out where this auto-fill is coming from. Would any of you happen to know?

For reference, the auto-fill hyperlink is =HYPERLINK("//Atlas/Projects/11205 MIR",11205). That job was completed and moved to a separate table in a separate tab of the workbook in the middle of March 2018. None of the other columns of new rows auto-fill its corresponding content, just column A.

I appreciate any help you might be able to be.

Joshua Huff
  • 11
  • 1
  • Is it an xlsm file? For a macro, auto filling specific cells on worksheet change is trivial. – SBM Apr 24 '20 at 12:58
  • @SBM it finally allowed me to comment directly on my post. No, it's an xlsx file. – Joshua Huff Apr 24 '20 at 13:15
  • Well, the only non macro trick I've used for such a purpose was prematurely filling the entire column (up to a point) with a condition which leaves it blank until any other columns are filled, eg: =IF(COUNTIF(B1:K1,"<>")>0,"filled","") . I guess that's not the case as you've quoted the link verbatim. Would need to see the file to get a better idea of how it might work. – SBM Apr 24 '20 at 13:55
  • Here's a link to a stripped version of the spreadsheet. All personal information has been removed. http://www.mediafire.com/file/370fqckrsjhrqzw/MIR_Jobs_SuperUserVersion.xlsx/file – Joshua Huff Apr 24 '20 at 14:48
  • Alright, I can see that this section is defined as a "table" - if you right click "file no." and select table -> "convert to range" this autofilling behaviour goes away. I'll see what I can find on the subject. – SBM Apr 24 '20 at 14:56
  • Right, it's a calculated column: https://support.office.com/en-us/article/use-calculated-columns-in-an-excel-table-873fbac6-7110-4300-8f6f-aafa2ea11ce8 – SBM Apr 24 '20 at 15:01
  • That's odd—the "Fill formulas in tables to create calculated columns" box was already unchecked when I looked at those settings. However, checking the box made the issue stop; then, I went back and unchecked it like it was before and the issue still appears to have stopped. Regardless of that confusion, the problem is solved. – Joshua Huff Apr 24 '20 at 15:13
  • If you would please post a response so I can mark it as the correct answer, I would appreciate it. – Joshua Huff Apr 24 '20 at 15:14
  • As you wish, sir. I'm pretty pleased just getting to know this feature, as it's better than what I've hacked together in the past in some cases. Though an optional "activate formula then paste special" or "auto sort" would make it even more robust. – SBM Apr 24 '20 at 16:38
  • And about that tickbox, it seems rather unreliable in my version (good ol' 2007) as well. – SBM Apr 24 '20 at 18:54

1 Answers1

1

There are 3 methods of automatically filling cells of which I'm aware:

  1. A worksheet_change macro, which fills a specific column based on the cell's position. The file must be of type xls / xlsm to store macros, and the offending code viewed in Alt+F11.

  2. The column is filled with a formula containing a condition based on the content status of other columns:

    =IF(COUNTIF(B1:K1,"<>")>0,"filled","")

    The full formula is visible and editable in the cell as usual.

  3. As is this case, excel tables - https://support.office.com/en-us/article/Overview-of-Excel-tables-7AB0BB7D-3A9E-4B56-A3C9-6C94334E492C

    When a new column is created in a table and a formula entered (in this case, a hyperlink), the column becomes a calculated column (unless disabled) - https://support.office.com/en-us/article/use-calculated-columns-in-an-excel-table-873fbac6-7110-4300-8f6f-aafa2ea11ce8

    Meaning, the formula is automatically propagated to any existing and new rows in that column. This behaviour appears to persist somewhat despite deleting it in existing rows, when new rows are inserted - as noted in the post.

    Disabling calculated columns - https://stackoverflow.com/questions/40697706/how-do-i-prevent-excel-from-automatically-replicating-formulas-in-tables

    Detecting the existence of a table / calculated column - when right clicking any cell in the table, there will be a table oriented menu; for example rather than "Insert..." there's insert -> table columns / table rows.

SBM
  • 133
  • 6