Nov 26 2017
- last edited on
Jul 25 2018
I have a workbook with multiple sheets with a different table on each one. A lot of the columns needs consistent formulas and so calculated columns are great for this. However, some of the columns I don't want to have this enabled. It seems that any column that I would like this feature to work didn't work originally, and the columns that I don't want to use it with, it's working with.
I've figured out to make an existing column work when it didn't originally, I just have to make sure all the cells in that column have the same type of formula. However, as far as I can tell, once you set it up like that, you can't break it.
For example: I have a column that will automatically create a hyperlink to another part of the workbook. When I would create another row in the table, it wouldn't copy that formula. After making sure every cell in that column had the same formula structure, it works now. Elsewhere in the workbook (even in the same table), I have a column that also creates a hyperlink to somewhere else in the workbook. However, not ever cell in these columns has the same formula structures. Yet when I create a new row, it still copies down the formula.
Is there a way to stop this from happening?
Nov 26 2017 08:34 PM
@Benjamin Wooler wrote:
A lot of the columns needs consistent formulas and so calculated columns are great for this. However, some of the columns I don't want to have this enabled.
5th Excel commandment: Thou shalt never have different formulas in a column.
Nov 26 2017 08:36 PM
I know unless it's a hyperlink formula. Because I'm not going to have every cell take you to the same place and have the same friendly name.
Nov 26 2017 09:57 PM
Just add two additional columns - one with the link and one with friendly name - and refer to these cells in the hyperlink formula.
Nov 27 2017 04:52 AM
I can do that. But shouldn't there be a way to not have it copy cells down? Because not all of my columns copy the formulas down. And some have the same formula in all of it's cells.
Nov 27 2017 08:40 AM
Why not? It doesn't hurt. And there are over 16000 columns available.
You could enter any text or number in all of the column cells and then replace it one by one with the formulas. But that would violate the 5th Excel commandment. And you can't be sure that Excel doesn't pick up one of the formulas when entering new rows.
Nov 27 2017 08:45 AM
If I understand your suggestion correctly, that doesn't work. Currently, it doesn't matter what I have in those columns (even nothing), it still copies the formula down when I create a new row. At this point, I can't even change that formula because the formula that it's copying down doesn't exist in the column anymore.
Apr 07 2021 07:17 AM
I am trying to find out the same thing. this is very annoying. I have a column with calculated cells all the way down, but I want to enter a hard number as the first cell, and it is changing the entire column. it seems the response to this thread is very unhelpful. @Benjamin Wooler