Forum Discussion
autofill column only when data are added
I have a column (Column C) that calculates a formula based on the entry in another column (Column B). The spreadsheet will be used by other users. The issue is that I do not know how many entries any particular user will have. They may have 20 or they may have 200 or they may have 2000. I do not want to autofill the entire column with the formula because (a) it slows everything down, (b) it is ugly/not user-friendly (displaying #DIV/0! until the column it needs is filled, and (c) it can actually end up affecting the results. I've seen another sheet that does the same thing. For example, there are 22 entries right now. Cell B24 and C24 are currently blank - no formula is showing for cell C24. If I add a number to B24, then C24 auto-populates the formula and calculates it. I cannot figure out how to make my spreadsheet do that, despite much searching and exploration. I've tried various fill options, an arrayfunction, a table, and I haven't yet found a solution that work. Any help is appreciated!
ETA: Solution found. Thanks for reading 🙂
Do you work with structured table or with range? If the former and wrap the formula wit IFERROR it shall work.
- Except I don't want the formula in the cell at all until I enter the data in Column B. I wouldn't think it's possible, but I have an example spreadsheet that does it - I just can't figure out how.
HERE IS THE SOLUTION:
If you have FOUR (or more) autofilled functions, it will then continue to add them as more data are added. (I kept trying with only three).
