Magic formula appears (1 Viewer)

DanG

Registered User.
Local time
Today, 01:21
Joined
Nov 4, 2004
Messages
477
Hello,

I have an existing worksheet that has a table of data and column 1 had no data or formula in it unless numbers are entered into column 2 & 3.

I have seen this before but can't remember where, I think it has to do with tables?

Can anyone tell me how this is working and how I can change the range/formula?

Thank you
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:21
Joined
May 7, 2009
Messages
19,169
if it is a Table in excel, just change the formula in one row and it will be populated downward.
 

DanG

Registered User.
Local time
Today, 01:21
Joined
Nov 4, 2004
Messages
477
Thank you, the thing is there is no formula. A formula only appears in Columb 1 when Columb 2 & 3 have numbers entered into them. Otherwise it is completely empty.

It's magic I say! There is no vba involved.

Sent from my SAMSUNG-SM-N910A using Tapatalk
 

Gasman

Enthusiastic Amateur
Local time
Today, 08:21
Joined
Sep 21, 2011
Messages
14,037
Are you sure there is nothing in the worksheet change event?
Upload the workbook?
 

DanG

Registered User.
Local time
Today, 01:21
Joined
Nov 4, 2004
Messages
477
Not that I see, I will upload tomorrow as it's on my work computer.

Sent from my SAMSUNG-SM-N910A using Tapatalk
 

DanG

Registered User.
Local time
Today, 01:21
Joined
Nov 4, 2004
Messages
477
Please see the attached example.

Make a new entry into column 2 & 3 and you will see a result appears. The thing is there is no formula in column until you enter into the other columns.

I am curious as to how this works
 

Attachments

  • Example.xlsx
    8.4 KB · Views: 148

Gasman

Enthusiastic Amateur
Local time
Today, 08:21
Joined
Sep 21, 2011
Messages
14,037
Neat, but no idea. :D
 

Minty

AWF VIP
Local time
Today, 08:21
Joined
Jul 26, 2013
Messages
10,353
That makes two of us.

Not a clue ! (And I thought I knew my way around Excel a bit?!)
 

isladogs

MVP / VIP
Local time
Today, 08:21
Joined
Jan 14, 2017
Messages
18,186
Its somehow autofilling from the formula in the previous row.
If you leave a row blank e.g. row 5 then enter data in the following row (6) the formula doesn't appear in A6.
Now go back to row 5 and complete that. That works as 'normal'
The formula still doesn't appear in A6
Edit B6 or C6 - still nothing in A6.

I thought at first there might be a formula hidden in a later column or row but it appears not.

I copied the first two rows to a new sheet. The behaviour isn't replicated
Similarly it isn't replicated if you paste the first two rows to different columns on the same sheet.

Nothing obvious in Excel options either. I'm equally baffled.:(
 

Micron

AWF VIP
Local time
Today, 04:21
Joined
Oct 20, 2018
Messages
3,476
Options > Advanced

check/uncheck "extend data range formats and formulas"
 

isladogs

MVP / VIP
Local time
Today, 08:21
Joined
Jan 14, 2017
Messages
18,186
Hi Micron
That partly explains it in that the magic stops when its unticked.
However why when its ticked doesn't it work when I try elsewhere on the worksheet or on a different worksheet?
 

Micron

AWF VIP
Local time
Today, 04:21
Joined
Oct 20, 2018
Messages
3,476
You need at least 3 of the last 5 rows to contain the formula. If you go to a new sheet or range and enter 5 rows of figures, insert the formula in the first cell and drag down, it will work thereafter. In dragging, I'm assuming you have allowed the option to extend formulas. Also assuming you tried in a new sheet/range but didn't quite set the pattern to be followed.
 

DanG

Registered User.
Local time
Today, 01:21
Joined
Nov 4, 2004
Messages
477
You need at least 3 of the last 5 rows to contain the formula. If you go to a new sheet or range and enter 5 rows of figures, insert the formula in the first cell and drag down, it will work thereafter. In dragging, I'm assuming you have allowed the option to extend formulas. Also assuming you tried in a new sheet/range but didn't quite set the pattern to be followed.

Mystery solved!

Thank you very much, kind of a neat trick.
 

isladogs

MVP / VIP
Local time
Today, 08:21
Joined
Jan 14, 2017
Messages
18,186
Thanks Micron

I guessed it was something like that but didn't know the 'at least 3 out of 5' rule.
The autofill will still only work if the previous row contains the formula
That's why it fails if you omit a row even if you then go back and fill that afterwards

FYI Just awarded you some well deserved reputation points.
 

Micron

AWF VIP
Local time
Today, 04:21
Joined
Oct 20, 2018
Messages
3,476
The autofill will still only work if the previous row contains the formula
I should have written "in at least 3 of the last 5 preceding rows". I looked it up based on your comments to see if the documentation covers this. Turns out I wasn't recalling it quite correctly. Amazing what missing one word can mean. Might as well post the link as it's for other advanced options as well.
https://support.office.com/en-us/article/advanced-options-33244b32-fe79-4579-91a6-48b3be0377c4


re your FYI: thanks, but you are too kind, Obi-Wan :)
 

isladogs

MVP / VIP
Local time
Today, 08:21
Joined
Jan 14, 2017
Messages
18,186
The link you provided says:
Extend data range formats and formulas

Select to automatically format new items added to the end of a list to match the format of the rest of the list. Formulas that are repeated in every row are also copied. To be extended, formats and formulas must appear in at least three of the five last rows preceding the new row.

Missing a row doesn't flout what the final sentence says.
I think what matters is the preceding sentence that I've marked in bold
 

Micron

AWF VIP
Local time
Today, 04:21
Joined
Oct 20, 2018
Messages
3,476
Not sure I follow your meaning based on my testing. I removed 2rd last formula (which appears here as the 3rd last row) and got an automatic result of 33% when I entered values 5, 15 in the last row but data was in the row (60, 5) following before I removed the formula.
75% 8 6
63% 8 5
50% 8 4
50% 8 4

8% 60 5
33% 15 5
Interesting enough, the % autocompletes if you go back and enter values.
No matter. At least we know where the magic comes from.:)
 

DanG

Registered User.
Local time
Today, 01:21
Joined
Nov 4, 2004
Messages
477
I should have written "in at least 3 of the last 5 preceding rows". I looked it up based on your comments to see if the documentation covers this. Turns out I wasn't recalling it quite correctly. Amazing what missing one word can mean. Might as well post the link as it's for other advanced options as well.
https://support.office.com/en-us/article/advanced-options-33244b32-fe79-4579-91a6-48b3be0377c4


re your FYI: thanks, but you are too kind, Obi-Wan :)

I see in your profile you're retired....keep giving answers like this and you'll find yourself working again :)....I wouldn't wish that on anyone, thanks again!
 

Micron

AWF VIP
Local time
Today, 04:21
Joined
Oct 20, 2018
Messages
3,476
I see in your profile you're retired....keep giving answers like this and you'll find yourself working again :)....I wouldn't wish that on anyone, thanks again!
Actually, I wouldn't mind being called back to do this sort of stuff. Has happened twice already, but I don't foresee it happening again. Double dipping is not hard to take.
Thanks.
 

Users who are viewing this thread

Top Bottom