How to copy column range to another column using formula in 1 cell?

[romis]

Hello,

How to copy column range to another column only using formula in one/first cell? So if I change something in COL_1, I immediately see the same changes in COL_2.

 

[Fluff]

As far as I know, that is not possible using Excel 2019.

 

[Joe4]

You would have to put the following formula in cell F3:
=A3
and copy down for all your data rows.

Note that you CANNOT put a formula in one cell, and have it populate other cells (using just formulas). Formulas can only return values to the cells they are put in. They cannot "write" information to cells other than the one they are put in.

To have it do it automatically with only populating one cell would require VBA. If you are interested in seeing a VBA solution, please explain the exact logic, such as:
1. What is "triggering" it to run? More specifically, what cells are we watching to be updated to cause it to run?
2. Once it is triggered to run, what is the logic for determing exactly which column to copy from? It may be obvious if there is only one other populated column, but what if multiple columns are already populated on the worksheet?

 

[romis]

The trigger is the change of information in source column. I didn't think it may require VBA.
What about using array?

 

[Fluff]

To do it with a formula in a single cell would require 365 with dynamic arrays. Why can't you use a formula in multiple cells?

 

[romis]

To do it with a formula in a single cell would require 365 with dynamic arrays. Why can't you use a formula in multiple cells?

I do not want to use formula in multiple cells, because if someone will delete, insert, cut&paste data in the source column, my COL_2 formulas and order will messed up.

 

[Fluff]

In that case you will need to answer Joe's questions in post#3

 

[romis]

In that case you will need to answer Joe's questions in post#3

I found solution to not mess-up formulas, when changing sourse column, using this copied down formula, because the range A:A never change: =OFFSET(A:A;2;0;ROWS(A:A)-2)

 

[Fluff]

Glad you solved it & thanks for letting us know.