Forum Discussion

JimGleason's avatar
JimGleason
Copper Contributor
Mar 28, 2022

Why formula autofills in some columns, but not others?

I have a financial spreadsheet that performs calcs on multiple accounts based on daily entries in the first few columns. When I fill in the source data, the calcs autofill (auto-populate?) to the rig...
excel
Formulas and Functions
Martin_Weiss's avatar
Martin_Weiss
Bronze Contributor
Mar 30, 2022

Hi JimGleason 

 

usually, you just need to re-enter the formulas in the very first row of the respective columns. Just select the first formula, press F2 for edit-mode and then just enter. 

This usually triggers the autofill function for the whole column again.

JimGleason's avatar
JimGleason
Copper Contributor
Mar 31, 2022
Thanks, Martin_Weiss, I'll give it a whirl. Let you know how it pans out.
  • JimGleason's avatar
    JimGleason
    Copper Contributor
    Apr 06, 2022
    Hi Martin_Weiss. No joy. I tried rekeying the formula into the top row - next day, that column still didn't copy the row into the new row. Yesterday, I rekeyed the formula up top, and cut and paste it individually into every cell below in the column ... and today, that column did not replicate the formula when the source data columns were filled. Other columns populated, but not this one.

    Hmm.
    • Martin_Weiss's avatar
      Martin_Weiss
      Bronze Contributor
      Apr 07, 2022

      Hi JimGleason 

       

      maybe you could upload a sample file with non-sensitive data, so we can investigate easier.

      • JimGleason's avatar
        JimGleason
        Copper Contributor
        Apr 20, 2022

        Hi Martin_Weiss  --

         

        Sorry it took a while. I've attached (uploaded) a stripped down and abstracted core of the original spreadsheet.

         

        It is admittedly quite the three-ring circus (derived (comparative) fields - MANY feeding charts below the table).

         

        Daily (on business trading days), I enter the date (in col A), three indexes (DJIA/S&P/Nasdaq in cols B,D,F) and closing balances of an assortment of accounts (H,J,L,N for Fidelity - summed into Q, and U,W for Principal and T Rowe accounts).

         

        All columns to the right autopopulate (replicating formulas from rows above) ... well, not all - you may notice many columns are stubbed down for a whole year worth of entries because I haven't been able to figure out why they don't replicate.

         

        OF PARTICULAR INTEREST are columns ET,EU,EV - they don't replicate, though they're functionally equivalent to FE,FF,FG (same calcs, but on a different account).

         

        When I enter a value in col J (Fidelity IRA), CX poplulates (as it should), and in turn FE (again, as expected),
        but when I enter a value in col H (Fidelity IND), though CP populates, ET remains blank
        (formula does not autopopulate).

         

        Enter a new row and see for yourself. I've been having to manually copy the formula from the previous row and paste below - once I manually copy those three columns for that new row, the rest of the columns for that section fill on their own.

         

        The weird thing is, these same columns autopopulated on last year's spreadsheet. I don't know what changed when I flushed it for this year's spreadsheet.

         

        I'm not just looking to have it fixed, I'm wanting to understand, to know what to look for - is there a cell/column property to look for? Did something peculiar happen in a header row that disables the autopopulate for those columns? Why are columns for other accounts (like FE,FF,FG for the IRA) still autopopulating)?

         

        And what is the proper term for this kind of behavior on a new row? I'm not sure it's autofill. When I look up autofill, I get tips on how to drag a corner of the autofill gizmo... but nothing about a formula replicating when its dependent fields are filled.

         

        Any insight would be appreciated!

        Thanks

         

Resources