Forum Discussion
Why formula autofills in some columns, but not others?
Hmm.
Hi JimGleason
maybe you could upload a sample file with non-sensitive data, so we can investigate easier.
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
Hi JimGleason
thanks for sharing your file, now I have some answers for you.
My original assumption was, that you use formatted tables, which is a special functionality in Excel (menu "Home | Format as Table"), which would autofill/autopoplulate formulas once you add new lines.
But you are not using formatted tables, just regular lists, so my assumption was wrong. So much just for the records 😉
The answer to your problem is very simple: Certain columns do contain already formulas and others don't. The reason why they seem to "magically" autofill are conditinally formatting rules, which makes them appear when a certain condition is met.
For example, columns EW, ET, EY... already contain formulas, but you cannot see them:
There are conditional formatting rules which make them red or green (and therefor visible) based on their cell values:
Columns ER, ET, EU... on the other hand are really empty and do not contain any formulas:
Hence nothing happens when you enter new lines.
So the solution is simple:
Just copy the formulas in the respective columns down as far as you need them, like it is the case in the other columns already.
- Hi Martin_Weiss. I will do so shortly. I will make a clone and strip it down (and retest to make sure the behavior is consistent).
Thank you for your patience,
JPG
