Can you turn off cascading formulas on Excel Tables?

[Mr_Phil]

Hi, thanks for looking. I am learning to use tables instead of ranges. I noticed that when I inserted a formula it immediately copied down the column to the end. I thought that was neat. So I filtered it and modified the formula for the filtered records and it did it again including the items I filtered out. I built this workbook as table based to learn about it since I ever did was name ranges. In this case the column is for a minimum order amt. I have tools, janitorial supplies, office supplies, equipment, and accessories. Each uses a different formula to determine the minimum based on if we can track usage and other criteria. Am I going to need a separate column for each category to get it's own formula? Is what I am doing not a good fit for tables?

I appreciate y'all looking and really appreciate all the education available here. Thank you.

 

[Alex Blakenburg]

I think that you are asking the wrong question.
Inconsistent formulas in a column is extremely bad design, and the better question would be how can you use a formula that can handle the different scenarios. Normally the formula for each category is the same but use different input parameters which you can do by geting data from a category lookup table which contains the different minumum order amount for each category, and any other thresholds or multipliers that are required for each category.

Even without a formal table Excel goes to great length to try and highlight inconsistent formula within a row eg in the below the 2nd data row was different to the 1st and 3rd row

If you are set on modifying Excel Table behaviour, here is a link that will give you some options.
Note: If you turn of the Table formula autofill, it will impact the behaviour of your Excel, not just that table.
(Also they are your settings and won't flow on to other users. Other users will most likely also expect the formula to be consistent within the column)

How to Prevent or Disable Auto Fill in Table Formulas - Excel Campus

 

[Mr_Phil]

I think that you are asking the wrong question.
Inconsistent formulas in a column is extremely bad design, and the better question would be how can you use a formula that can handle the different scenarios. Normally the formula for each category is the same but use different input parameters which you can do by geting data from a category lookup table which contains the different minumum order amount for each category, and any other thresholds or multipliers that are required for each category.

Even without a formal table Excel goes to great length to try and highlight inconsistent formula within a row eg in the below the 2nd data row was different to the 1st and 3rd row

View attachment 80242
If you are set on modifying Excel Table behaviour, here is a link that will give you some options.
Note: If you turn of the Table formula autofill, it will impact the behaviour of your Excel, not just that table.
(Also they are your settings and won't flow on to other users. Other users will most likely also expect the formula to be consistent within the column)

How to Prevent or Disable Auto Fill in Table Formulas - Excel Campus

I have seen the warning triangle. I get what you're saying though. A dbms wouldn't tolerate some of the crimes against logic I've done in excel. I'm just trying to teach myself new tricks as it were. I have some older workbooks that could use updating and I thought before doing it that I could look at some ways and means I have not tried. Tables were the one that sounded easiest to acclimate to. I appreciate the advice and the link. The link also answered the question so you get the solving points Thank you.

 

[Alex Blakenburg]

By you referencing a dbms I know we are on the same page.
I hope you are giving your table meaningful names .
I prefer to prefix them with something like tbl so they sort together in the names manager and if you start typing "=tbl" in the formula bar you get a list of your tables.