Forum Discussion
Why formula autofills in some columns, but not others?
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 --
OK, here's me feeling like an idiot 😄 - I was so under the assumption that this family of formulas autogenerated row to row that when I cleared out the spreadsheet for the new year, I cleared out columns ET-EV.
But staying on the same spreadsheet, why do columns like ER and ES autogenerate on a new row when source data is provided, but columns like ET don't?
Go to the next available Row (with blank date and source data) and confirm that ER and ES and ET are blank (no hidden formulas that display a blank since source data hasn't been provided yet).
Rather than experimenting with permutations of what order source data is provided, fill in columns left to right starting with Date (A).
You will find that when you fill in "NASDAQ" (F), *PRESTO* - ER and ES copy the formula down into the current row...
... but when you fill in "Fidelity Ind" (H), ET fails to copy the formula down.
It occurs to me now that it *could be* because ET is dependent on CP, which is a column with prefilled formulas that display blank if *its* source data is blank. I don't recall why I needed to do that (*it* was clearly not autogenerating if I felt the need to add the IF clause displaying blank). Because it was already filled (with a self-hiding formula) that ET didn't think autogenerate is needed?
Let me go back and experiment a bit.
THANKS for pointing out my blunder (my assumption that FE was autogenerating).
JPG