Why formula autofills in some columns, but not others?

Copper Contributor

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 right for most, but not all columns. They are (nearly) identical calculations (differing only in which columns to source the data). They worked on last year's spreadsheet, but when I cleared the data for this year's spreadsheet, this problem emerged. Usually after the first few rows, autofill(/auto-populate) kicks in, but not this time for ALL columns, just most of them. What changed?

 

There is obviously SOMETHING preventing those few columns from auto-populating when all the other columns populate just fine. What should I look for?

 

THOUGH I *could* start over again (make a new copy of last year's spreadsheet, strip it down and hope that auto-populate remains), I'd rather understand *WHY* it's not working and actually fix it.

8 Replies

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.

Thanks, @Martin_Weiss, I'll give it a whirl. Let you know how it pans out.
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.

Hi @JimGleason 

 

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

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

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:

DTE_0-1650871199315.png

There are conditional formatting rules which make them red or green (and therefor visible) based on their cell values:

DTE_1-1650871301910.png

 

Columns ER, ET, EU... on the other hand are really empty and do not contain any formulas:

DTE_2-1650871361621.png

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