Formulas Not Increasing Properly in Table

Hello,

In my sample provided I am having issues with the formulas properly as I add an entry to a data table. Entries are added in Column 1 ( then Column's 2, 3 & 4 fill in automatically. However, columns 3 & 4 do not increase properly to follow suit with the rest of the pattern of formulas. In fact, it changes the formula above the new entry.

So if I put in a new entry to the table in A5 and hit enter the formulas auto-fill as they should but changes the formulas already in place on C4 and D4 and I'm not sure why.

The yellow cell is a drop-down that being referenced.

Any assistance is much appreciated.

Check that you actually need the $ in the range for those 2 columns.
the way you have it written, those cell references will not update/ change to the next row, remove the $ and it will update down the column

cubangt,

I appreciate the response. I attempted doing this and it breaks the outcome of the formula in Column4 and defeats the purpose of the drop down.

What I am trying to accomplish can be found on the trumpexcel website named-ranges-in-excel (sorry it's not allowing me to post a link, image or video).
I am trying to do what he is doing but in a table format. Where I can add entries to my data list as time goes on.

I'm sure there is some sort of workaround as well which I am open to suggestions.

can you provide a sample of the expected results?

Say i add a new row under anger in columnA

What should/are you expect to see in columns B, C and D

When i added a new record, my data showed up in the dropdown and everything else was a copy of the row above it, which i understand you are trying to correct.
So if you can provide a example of the results you are expecting, it would help adjust the formulas to return what you expect.

So when you add a row it increases the formulas properly as it should in row 5 for example but then it changes the formula that was in place already in row 4. Then it even gives an error on row 4 (C4 & D4) that "The cell is inconsistent with the column formula". Attached is a revised version you asked for. Sheet1 is what happens when you add an entry to A5, notice the errors in C4 and D4. Sheet2 is what it should look like but I needed to manually correct the formula after adding an entry to A5.

thats very interesting..

i added a few rows to sheet 1 and from those rows down, they all ended up being the same, BUT, if i went up to row 2 and re-applied the formulas down, it corrected the data and shows as you want.

dont think ive seen that before, but im sure its just a matter of changing something simple..

ok found a solution and its very simple for you to update.

since you are working with a table, instead of referencing the cell reference the table column

Like so:

Column C set it up like this: =IF(B2=1,COUNTIF($B$2:[@Column2],1),"")

Column D set it up like this: =IFERROR(INDEX([Column1],MATCH(ROWS($C$2:[@Column3]),[Column3],0)),"")

apply it down the column then add a new row and should see your data correct.

You're a genius, my friend. I actually tried that at one point in time before posting to the forums but I hadn't tried doing it with the "at symbol"...mostly because I didn't know what it did. Would you be ever so kind as to educate me on what the "at symbol" does in these formulas?

Again, sorry, it won't actually let me post the "at symbol" because HTML recognizes that as a "link" that I'm not allowed to post yet.

Here is a good complete explanation on that, its a column specifier

column specifier(structured references)