IF condition is true, copy a value to multiple cells
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
Best Answer

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.
Answers

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
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.

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.
Help Article Resources
Categories
Check out the Formula Handbook template!