Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-04-2019, 10:21 AM   #1
DanG
Newly Registered User
 
DanG's Avatar
 
Join Date: Nov 2004
Posts: 477
Thanks: 1
Thanked 1 Time in 1 Post
DanG is an unknown quantity at this point
Magic formula appears

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

__________________
You just pick up a chord, go twang, and you're got music
~ Sid Vicious

I use A03
DanG is offline   Reply With Quote
Old 01-05-2019, 01:54 AM   #2
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,008
Thanks: 64
Thanked 2,550 Times in 2,448 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Magic formula appears

if it is a Table in excel, just change the formula in one row and it will be populated downward.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is online now   Reply With Quote
Old 01-05-2019, 03:46 PM   #3
DanG
Newly Registered User
 
DanG's Avatar
 
Join Date: Nov 2004
Posts: 477
Thanks: 1
Thanked 1 Time in 1 Post
DanG is an unknown quantity at this point
Re: Magic formula appears

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

__________________
You just pick up a chord, go twang, and you're got music
~ Sid Vicious

I use A03
DanG is offline   Reply With Quote
Old 01-06-2019, 03:29 AM   #4
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,979
Thanks: 417
Thanked 714 Times in 693 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Magic formula appears

Are you sure there is nothing in the worksheet change event?
Upload the workbook?
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 01-06-2019, 02:12 PM   #5
DanG
Newly Registered User
 
DanG's Avatar
 
Join Date: Nov 2004
Posts: 477
Thanks: 1
Thanked 1 Time in 1 Post
DanG is an unknown quantity at this point
Re: Magic formula appears

Not that I see, I will upload tomorrow as it's on my work computer.

Sent from my SAMSUNG-SM-N910A using Tapatalk
__________________
You just pick up a chord, go twang, and you're got music
~ Sid Vicious

I use A03
DanG is offline   Reply With Quote
Old 01-07-2019, 08:19 AM   #6
DanG
Newly Registered User
 
DanG's Avatar
 
Join Date: Nov 2004
Posts: 477
Thanks: 1
Thanked 1 Time in 1 Post
DanG is an unknown quantity at this point
Re: Magic formula appears

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
Attached Files
File Type: xlsx Example.xlsx (8.4 KB, 40 views)
__________________
You just pick up a chord, go twang, and you're got music
~ Sid Vicious

I use A03
DanG is offline   Reply With Quote
Old 01-07-2019, 08:59 AM   #7
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,979
Thanks: 417
Thanked 714 Times in 693 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Magic formula appears

Neat, but no idea.

__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 01-07-2019, 09:33 AM   #8
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,357
Thanks: 159
Thanked 1,707 Times in 1,677 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Magic formula appears

That makes two of us.

Not a clue ! (And I thought I knew my way around Excel a bit?!)
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 01-07-2019, 10:03 AM   #9
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,085
Thanks: 110
Thanked 2,739 Times in 2,500 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Magic formula appears

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.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 01-07-2019, 10:59 AM   #10
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 865
Thanks: 10
Thanked 181 Times in 171 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Magic formula appears

Options > Advanced

check/uncheck "extend data range formats and formulas"
Micron is offline   Reply With Quote
Old 01-07-2019, 11:03 AM   #11
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,085
Thanks: 110
Thanked 2,739 Times in 2,500 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Magic formula appears

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?
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 01-07-2019, 11:14 AM   #12
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 865
Thanks: 10
Thanked 181 Times in 171 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Magic formula appears

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.
Micron is offline   Reply With Quote
The Following 2 Users Say Thank You to Micron For This Useful Post:
DanG (01-07-2019), isladogs (01-07-2019)
Old 01-07-2019, 12:06 PM   #13
DanG
Newly Registered User
 
DanG's Avatar
 
Join Date: Nov 2004
Posts: 477
Thanks: 1
Thanked 1 Time in 1 Post
DanG is an unknown quantity at this point
Re: Magic formula appears

Quote:
Originally Posted by Micron View Post
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.
__________________
You just pick up a chord, go twang, and you're got music
~ Sid Vicious

I use A03
DanG is offline   Reply With Quote
Old 01-07-2019, 12:24 PM   #14
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,085
Thanks: 110
Thanked 2,739 Times in 2,500 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Magic formula appears

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.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 01-07-2019, 12:47 PM   #15
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 865
Thanks: 10
Thanked 181 Times in 171 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Magic formula appears

Quote:
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/art...6-48b3be0377c4


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

Micron is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Magic Jack Thales750 Other Software 5 03-26-2013 04:21 PM
Bob has worked his magic Brianwarnock The Watercooler 33 11-17-2007 10:22 PM
magic eight ball arkres General 12 10-01-2003 12:03 AM
What is the magic code? Benny Wong Forms 1 11-12-2002 01:26 AM
Magic subforms djh Forms 8 07-20-2000 12:15 AM




All times are GMT -8. The time now is 09:09 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World