Getting Cell Entry from Previous Cell

[RobertWA]

In a table I want a column in which each cell value depends on the value in the previous cell in the same column. For example, each entry to be double (or half, or the square of, etc) the value in the previous cell.

And I want this process to stop after a specified number of cells have been filled.

How can I do this, please?

Thanks,

Robert

[kuschem]

Hello Robert,

please be more specific or attach a sample file illustrating with what you are trying to achieve.

[jason.b75]

In A1 enter the number of cells to fill.
In A2 enter the starting number.
In A3 enter this formula, =IF(ROWS(A$3:A3)>A$1,"",A2)

A2 at the end of the formula should be edited with the desired calculation.

[RobertWA]

Hi Jason,

Thanks, but it didn’t it let me achieve exactly what I wanted to do.

Having entered the formula in A3 (and editing the last part, eg A2*2) I still had to drag that formula down the column to accommodate the number of cells I wanted filled. Certainly if I dragged too far the formula caused the entries to stop at the correct number.

Is there any way to achieve this automatically, ie once the formula is entered in Cell A3 it goes ahead and fills the required number of cells with no further input from the user?

So what I am after is this:
A1 I enter the number of cells I want filled. eg 6
A2 I enter the starting number. eg 5
A3 Here I put the formula, eg something to double the previous entry. So in my example the entry in A3 becomes 10 and the rest of the entries (A4 to A7) are generated without any further action by me.
A4 20
A5 40
A6 80
A7 160 stopping there, since I now have my 6 entries from 5 to 160.

How can I do this?

Robert

[AliGW]

You only have to enter and drag the formula down once. Drag it down further than you are ever likely to need. Then it’s just a case of changing values in the input cells.

If you want to add an extra calculation to the formula, then it needs to be in an additional input cell, not in the formula itself.

[jason.b75]

As Ali suggested, just drag it down beyond the number of rows you are ever likely to use. If you format the column as a table then changing the first formula from A2*2 to A2/2 as needed should modify the rest of the column without needing to fill down again.

For a formula to be automatically entered into additional rows, you would need to enter data manually into the preceding column, so this will not work for you.

The only other option is with vba, but given that you want to change the criteria of the formula, again, this could get messy.

[RobertWA]

Thanks Jason and Ali.

Robert

[AliGW]

You're welcome!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.