Code to fill formula in merged cells & unmerged cells

[ExcelRoy]

Hi all,

Hope you can help

I am looking for a some code to enter formula in cells J13:J2011

I am looking for the following

J13 (merged with J14)
=IF(Update!L3="","",Update!L3)

J15 (Merged with J16)
=IF(Update!L4="","",Update!L4)

J17 (merged with J18)
=IF(Update!L5="","",Update!L5)

etc etc

I know merged cells isn't the answer but it achieves what i need
Simpel dragging down teh firmual jumps from L3>L7 etc missing critical data

Many thanks for any help

 

[6StringJazzer]

Merged cells are evil. Do not use them. This is one reason why. You have not shown your data but there is always a better way to design your layout than to use merged cells.

Here is a formula that will work on your highly undesirable format.

=LET(U,INDEX(Update!L:L,(ROW()-13)/2+3),IF(U="","",U))

 

[ExcelRoy]

Wow, wont even begin to work out how this works but perfect thanks!

 

[6StringJazzer]

LET is a new function that allows you to assign an expression to a variable so you can use that variable in your formula instead of repeating a long expression more than once. The variable U represents the expression

INDEX(Update!L:L,(ROW()-13)/2+3)

which returns the desired cell on Update.

INDEX returns a cell, based on a range of cells and a row and column within that range. Update!L:L contains all the cells you want to reference. ROW() is the row number for where this formula is. If we do the arithmetic (13 is the first row containing your formula, 3 is the first row to reference in Update):

(ROW()-13)/2+3

Then you get:

Row number of formula	Result of expression
13	3
15	4
17	5

and so on.

So the formula says if the referenced cell (U) is blank, then return a blank result, otherwise return U.

 

[ExcelRoy]

Thanks for the explanation, much appreciated