Working with Lists (Now Tables) in Excel 2007

[Christie P]

I have a spreadsheet that is dominated by lists. I have sheets where there
are lists beside lists beside lists. ANd I loved the "add a new row" feature
in 2003. I'm not that thrilled about having to put my cursor in the LAST
column of my "table" and hitting TAB to add a new row - but I suppose I can
live with it.

OK My real dilemma is - I have formulas EVERYWHERE that refer to the entire
contents of my list/table. And in 2003, whenever I added a new row, those
formulas updated to include that new row. Apparently in 2007, they don't.
HELP! I can't spend my life updating formulas just because I added a new
row. Any ideas?

 

[NBVC]

If you created the table properly through Insert|Table in 2007, then it
works similar to List in 2003

When you enter anything in a "text field" in the next row, the formulas
will auto-insert into the appropriate spots..

The only thing you don't see the * in the next row like you did in
2003, but you see the little "corner arrow" icon in the bottom right
corner of the table indicating that you can expand the table...

 

[Christie P]

Thank you for your response.

I created this spreadsheet in 2003 - and just today opened it for the first
time in 2007 - so all the lists / tables were created in Excel 2003.

I do see where any formula in a previous row is auto-inserted into my new
row. That does work.

However, I have formulas outside of my list/table which refer to all the
rows in my list/table. And in 2003, those formulas would change to include
my new row. In 2007, I do not see those formulas adjusting to the presence
of my new row.

That's my main issue.

As for the little "corner arrow" - that doesn't meet my needs because I do
have data below my list/table that I do not want to include in my list/table.
If I use the "corner arrow", the table will now include that data. I want
the data below my table to stay below my table - to be moved down one row
when I insert a new row. Putting the cursor in the last column and hitting
TAB will move the data below down one, so does Right-Click and hitting
Insert--> Below.

 

[NBVC]

I just did a quick test and created a list in XL2003

Simply

X Y
1 =A2
2 =A3
3 =A4


and then I have a Formula separately: =Sum(B2:B4)

When I add a row to the list, the formula updates to say =Sum(B2:B5)
as expected..

I saved the file and opened it in XL2007 and when I add a row to th
now "table" it updates the other formula to =Sum(B2:B6) as expected.

Is this a similar situation to yours

 

[Christie P]

Yes - that is similar to my situation.
I've played with it a bit more, and I've seen where sometimes the external
formula updated, and sometimes it did not.

It might be the case where the formula does not update if I insert multiple
rows at one time without actually putting data in them.

But I have seen it update the formula too - so I'll just have to keep an eye