Table not acting as expected

[Russk68]

I have a simple formula in a table ='Helper Cable Build'!G6 down to row 1000. When i add a row to the table, the formula is copied from above. So I wll get 2 rows with a reference to G6 instead of G6,G7. Im on a Mac and have Auto expand tables and auto fill formulas checked. The table starts in row 5 where my headers are.

 

[Sergius]

This should help. Add $ sign before G

='Helper Cable Build'!$G6

 

[Russk68]

Same resu

This should help. Add $ sign before G

='Helper Cable Build'!$G6

lt with the $G6

 

[Joe4]

This should help. Add $ sign before G

='Helper Cable Build'!$G6

I don't see how that would help, as adding the "$" simply locks the column reference.
But it sounds like he is saying that the row reference is acting like it is locked, even though there is no $ in front of it.
He wants the row to behave like it isn't locked.

I ma not sure if it is a Mac thing, but it would be helpful if you could provide more information.
How many rows does this table contain to start?
If you look at any other row in the same column in this table, what does it show for that formula?
If every single row says "='Helper Cable Build'!G6[" to start, I can understand how inserting any new rows would do the same.

 

[Russk68]

I don't see how that would help, as adding the "$" simply locks the column reference.
But it sounds like he is saying that the row reference is acting like it is locked, even though there is no $ in front of it.
He wants the row to behave like it isn't locked.

I ma not sure if it is a Mac thing, but it would be helpful if you could provide more information.
How many rows does this table contain to start?
If you look at any other row in the same column in this table, what does it show for that formula?
If every single row says "='Helper Cable Build'!G6[" to start, I can understand how inserting any new rows would do the same.

The table starts at row 5 with the header and continues to 1006. When I enter the formula in row 6, ='Helper Cable Build'!$G6, the rest of the column in the table populates with G7 in the next row and G8 and so on. This is expected, as its a table. And again as I just tried it, the row I added is a duplicate from the one above. Ive been using Excel for 20+ years and never needed a table until now and I thought this was going to be an easy solution to add rows as its a feature of using tables. I'm stumped.

 

[Joe4]

OK, I did some playing around, and I created a table that has 3 rows, and I entered this formula in the first record of my table:

=Sheet1!A1

and then it populated it down for the other two rows like this automatically:

=Sheet1!A2
=Sheet1!A3

However, when I went to insert a new row between row 1 and row 2, it repeated

=Sheet1!A1

in this new row.

This kind of makes sense, when you think about it. Since you already have formulas increasing by one for every row that you go down, the second record in your table (before the insert) is associated with a certain record from the other sheet. Inserting a new record, Excel isn't sure what you want to do with that. There is a very good chance you may not want to change the current record associations with every record below that. So it simply copies the formula from the row above.

Note however if you select the original formula in the first row again, and double-click on the lower right corner of that cell, it will re-do the autofill, renumbering all those formulas, so it will change from (in my example):

=Sheet1!A1
=Sheet1!A1
=Sheet1!A2
=Sheet1!A3

to:

=Sheet1!A1
=Sheet1!A2
=Sheet1!A3
=Sheet1!A4

So I would say this is probably expected, justifiable behavior.
I think Microsoft sometimes gets itself in trouble when it assumes too much (which is why I hate Word so much!). I would rather it not assume anything and let me make the decision.

 

[Russk68]

Now that you confirmed that its not just me, I will add a mcro that will fill down the formulas to compensate for the added row. I just thought that tables were meant for this type of task.
Thanks Joe!

 

[Joe4]

Now that you confirmed that its not just me, I will add a mcro that will fill down the formulas to compensate for the added row. I just thought that tables were meant for this type of task.
Thanks Joe!

You are welcome.

Tables can handle this, but it all depends on how you are using them. The structure you are showing is not that common. More often not, you would have some sort of lookup or match formula, and not a direct range reference where you want it to increment by one row each time. Then there becomes some uncertainty on what to do when you go to insert a new row in the middle of two existing rows. Should it re-number all the existing rows? Will you be inserting a new row in the other table? If so, have you already done that?

I think there is too much uncertainty to make a good decision (it would need to make some big assumptions), so it just copies the value from the row above.

Quite frankly, I seldom use tables myself. They seem to be more difficult to deal with, and I seldom have need to have to use them.

 

[Alex Blakenburg]

Now that you confirmed that its not just me, I will add a mcro that will fill down the formulas to compensate for the added row. I just thought that tables were meant for this type of task.
Thanks Joe!

The method you are using ie linking 1 sheet to another row by row is extremely fragile with or without using a table. It would be better if you explained and show us the full scenario so we can have a look to see if there are alternatives. The worst part of that method is that the rows will get out of sync and there will be nothing obvious to indicate that it has happened.
Ideally you want to link using a unique reference and use a lookup to get the data so that inserting or deleting rows mean you still return the right rows.
If you are going to use a macro anyway there are more options.
If you are going to use a Macro on a Table (ListObject) you want to reapply the formula to "all rows at once" otherwise it will not recognise the formula change as being its default value when rows are added to the table.