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.

  • Is it an xlsm file? For a macro, auto filling specific cells on worksheet change is trivial. – SBM Apr 24 at 12:58
  • @SBM it finally allowed me to comment directly on my post. No, it's an xlsx file. – Joshua Huff Apr 24 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 at 13:55
  • Here's a link to a stripped version of the spreadsheet. All personal information has been removed. mediafire.com/file/370fqckrsjhrqzw/… – Joshua Huff Apr 24 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 at 14:56
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.

| improve this answer | |

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.