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 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