IF condition is true, copy a value to multiple cells

Douglas Jerum

I have a sheet where users have to fill in 20 columns with numeric data. In some cases the data will be exactly the same for all 20 columns. In that case, I want to give them the option to select a dropdown that says all values are the same, and then use a helper column that triggers from that to copy the value from cell 1 of 20 to the other 19. I can't put a formula in the other 19 cells because the user may be overwriting it with manual data entry.

Is there a function that would allow me to copy the contents of cell 1 to the other 19 cells?

Thanks

Paul Newcome

I would suggest 20 helper columns. I know it sounds like a lot, but it will be able to automate what you want. Each of the helper columns would have a formula that looks something like this...

=IF($[Checkbox Column]@row = 1, $[Column 1]@row, [Column 1]@row)

You can put this in the first helper column and then dragfill over. It basically says that if the box is checked, insert whatever is in [Column 1], otherwise insert what is in [Column 1]. As you dragfill over the second [Column 1] reference will update to [Column 2], [Column 3], [Column 4], so on and so forth.

So while it is a lot of helper columns, it is a single formula that can be quickly dragfilled and will accomplish what you are trying to do.

If you are wanting to keep the sheet looking cleaner, you could even hide the columns that are filled in through the form so that only the formula columns are visible.

Stefan

Hi @Douglas Jerum ,

there is no function that I'm aware of, that does what you are asking for.

But... how about asking the users to fill in cell1 first and have the other 19 cells filled with the formula to copy cell1? If it happens most of the time, that the value of cell1 is used in every or many other cells, it could be still a time saver. You could describe this procedure in a text cell in the sheet.

You could also use a helper column as input for for all 20 cells and tell the users, that if they don't want this to happen they should start entering data from cell 1 right away. The 20 cells would still have the formula to copy the input cell, but this can easily be overwritten.

Hope this helps

Stefan

Paul Newcome

I would suggest 20 helper columns. I know it sounds like a lot, but it will be able to automate what you want. Each of the helper columns would have a formula that looks something like this...

=IF($[Checkbox Column]@row = 1, $[Column 1]@row, [Column 1]@row)

You can put this in the first helper column and then dragfill over. It basically says that if the box is checked, insert whatever is in [Column 1], otherwise insert what is in [Column 1]. As you dragfill over the second [Column 1] reference will update to [Column 2], [Column 3], [Column 4], so on and so forth.

So while it is a lot of helper columns, it is a single formula that can be quickly dragfilled and will accomplish what you are trying to do.

If you are wanting to keep the sheet looking cleaner, you could even hide the columns that are filled in through the form so that only the formula columns are visible.