Excel - How to Auto-Continue Formula to Next Cell?

[Bebot]

I don't know if I'll be able to describe it properly. I inherited an Excel file (author unknown) which requires me to enter some data into a particular column. As data is entered, the result derived from a formula in the next column of the row appears. Its just like the formula from the previous cell is copied down to the next cell automatically after my input. My observations:

1. There are no formulae extending down to unoccupied cells (no conditional formatting tricks here - reason given below in #6);

2. There are no macros running;

3. There is no data validation rule applied;

4. I am able to suppress this behavior when I go to the Options and under Edit tab I untick "Extend Data Range formats and formulae" but it is a simple sheet with no external data;

5. Citing #4, I am able to suppress the behavior but I am not able to duplicate it. If I do a cut-and-paste to another sheet, it doesn't work;

6. If there is a series of formula extending to several columns, the "automatic copy" seems to work only on the first one - which means the other cells in the new row that should contain formula are not copied. If this is a conditional formatting, it should have been applied to all the columns.

The only alternative for me to duplicate this behavior is to save the file under a different name and delete the data except the first row that contains the formula.

How do I emulate this behavior in my own worksheets and how do I extend the "automatic copy" to the other dependent columns?

 

[GlennUK]

You got it point 4. It's simply "Extend Data Range formats and formulae". If there 3 or 4 or 5 rows of data with formulae directly to the right, then typing in new data will automatically generate new formulae to match the new data. Simple as that.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[SkipVought]

The operative phrase is...

"...typing in new data..."

Skip,

_[red][/red]

 

[Bebot]

Thanks Glenn. I didn't realize Excel could do this. I tried out 1 or 2 lines and gave up. This is the first time I'm going to do data-entry in Excel that's and I am very impressed.