Extend a formula to next row only if the next row contains data

[mc2312]

Looking for a way to extend a formula to the next row but only if the next row contains data

e.g. if C1 contains =IF(A1="","",A1+10)

is there a way to automatically extend the formula to C2 if (and only if) data is entered into A2? And then on to C3, C4 etc.

The idea is to set up a template with only the formula in Row 1, and for the formula to copy down only as far as it is needed - to avoid having to copy the formula down to row 20,000!

I know excel will do this automatically if there are 4 similar rows, but in this situation only the top row can contain the formula.

Thanks!

 

[James006]

Hi,

If you were to convert your sheet to a table ... everything would be done automatically ... :wink:

 

[mc2312]

Thanks James! that makes sense and does answer my question! although it creates a new problem/question.. I didn't mention before that the number in A1 is being calculated using a formula referencing another cell (on another sheet). For this reason, the table is only picking up A1 and calculating C1, (because these cells contain the formulas) but it isn't generating row 2, because A2 is empty (because A2 needs the formula from A1 to be extended down).

So as well as needing the formula in C1 to extend down, I somehow need the formula in A1 to extend down, based on the number of rows used on the other sheet. I thought something using 'last row' maybe?

Hope that makes sense!

 

[James006]

You are welcome ...

If you are using a Table ... Row 1 is dedicated to Headers ...

and the first row with data / formulas is Row 2

What is the current formula in cell A1 which is based on the number of rows of another sheet ...?

 

[mc2312]

Ah, thanks for the clarification.

At the moment, in A1 I only have something like =IF(Othersheet!A1="","",Othersheet!A1) so currently it isn't based on the number of rows of the other sheet, I have just copied the formula down, estimating the number of rows I might need.. but the number of rows needed will vary each week so I'm just trying to see if there is a tidier way to do it, where the formula is copied down automatically in the new sheet to only as many rows as needed, based on the number of rows in the other sheet - rather than 'pre-copying' the formula to an estimated number of rows. (Just to clarify, the end result is that column A in the new sheet looks exactly the same as column A in the other sheet.) Hopefully again that makes sense! thank you!

 

[James006]

Quite honestly ... this is quite confusing ...

First step : do you now have a proper Table with your headers in Row 1 ... ???

 

[mc2312]

Thanks James, yes I appreciate it's confusing, sorry! I think it is probably because what I want to do can't be done?


Yes, i've set up a proper table with headings. I have a formula in B2 that references A2. If I paste the numbers 1 to 10 into A2-11, the formula in B2 is copied down to B11 and the values are calculated. All good. If I paste a formula that references another cell on another sheet into A2-A11, it calculates the new relative values and the formula in B2 is copied down to B11. All good.


The issue is that I want to set up a template that requires no pasting.. you just open it up, it references the cells on the other sheet to populate column A and then uses those values to calculate the values in column B. Creating a table doesn't help. The only way I have seen this done is by creating a formula in A2 and copying the formula down to A20,000 (or a very large number that is definitely larger than the number of rows you will need!). So the template has formulas in rows that won't be used. It just seems untidy..


Basically, what I'm trying to find is a formula for A2 on the new sheet that says "copy A2 on 'other sheet' to this cell, AND if A3 on 'other sheet' has a value, copy this formula to A3 on this sheet".. which would then tell A3 "copy A3 on 'other sheet' to this cell, AND if A4 on 'other sheet' has a value, copy this formula to A4 on this sheet" and so on.


The research I've done points towards a possible solution using LOOKUP to find the last row on the other sheet (and count the number of rows) or using COUNT to count the cells in column A with values - and use this number to define the number of times the formula has to be copied downwards from A2 on the new sheet.. but I haven't ben able to work out a solution.


Anyway, as I said, maybe it can't be done! I hope that makes more sense, thanks for your time!

 

[mc2312]

Actually, a better way of putting it is.. I'm trying to find is a formula for A2 on the new sheet that says "copy A2 on 'other sheet' to this cell, AND do this also for any cell below where the corresponding cell on ‘other sheet’ has a value“.

 

[James006]

What about :

=IF(Sheet2!A2<>"",Sheet2!A2,"")

HTH

 

[higrm]

It may be out of your hands, but why are you simply not using 'Othersheet' as a Table and put the formulae you want in that table? Ideally, you want to only have the data once. You can hide the columns if necessary and then if you need a different sheet, just use Table references in your formulae on this second sheet.