Can an Array formula copy a column from one sheet to another

English
, ,
#1

I have a column of numbers in a sheet called Tjson, that column gets new data added regularly. At this time I have an IF formula in another sheet, the formula in each cell checks if the cells in the 1st column are blank, if so it returns blank, if not blank it returns the value. This works, but I need to copy the formula into many thousands of cells in the column in the second sheet to ensure I have the most recent matching data.

The formulas in the second sheet look like this:-
=IF(Tjson.B1="","",Tjson.B1)
=IF(Tjson.B2="","",Tjson.B2)
=IF(Tjson.C3="","",Tjson.F3)
=IF(Tjson.C4="","",Tjson.F4)
etc etc.

Is it possible to do this with an array formula? I’m assuming it would make the spreadsheet more efficient to calculate, and be less prone to errors caused by a missing formula in any of the cells in the second sheet etc.

I’ve had a search, but have not found an answer

Thanks in advance

#3

Why not just =Tjson.B1? The result will be the similar, you can suppress 0’s with a format code of #.

1 Like
#4

Thanks - That’s a formatting option (#) that I was not aware of :grinning: I’m sure that I will use it in the future with smaller sheets, but it still needs a formula in many thousands of cells in my current spreadsheet, where an array is effectively only in one cell but ranged to many cells. If I can’t find an array formula to do the job, I’ll use the # to simplify the formulas I already have.

#5

(Not regarding precisely the words of the subject’s question.)

Advice:
Don’t use array formulas for such purposes.
Filling single-cell formulas down when needed is much simpler than scaling an array formula.
See example:
disask_138153_filling_conditional.ods (192.6 KB)

If you urgently want, you can automate the filling with the help of user code.

1 Like
#6

Arrays have some advantages:
If you insert or delete row(s) on the source range of the array, the references remains fine.
I think if there is a lot of formulas the size of the file is a bit lower.

#7

Though I don’t see this as realistic, I made a new examplre avoiding the erffects you dislike. See
disask_138153_filling_conditional_msv0.ods (196.4 KB).

I’m not quite sure how to understand this, but if you worry about too large files when using single-cell formulas: I tested it with the new example, and got a minor effect: 197KiB vs 166KiB.
This is with ods-zipping. I didn’t test with fods.

1 Like
#8

However, the output range remains the same, even if the result set has grown.

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.