how to have table insert formulas into cells when adding rows by adding data

[juntjoo]

I have some tables where upon adding data makes the table miraculously duplicate the formulas from the table into the new row making my work easy. Now trying to duplicate this function with a new different table it's not working. I'll add data into the row below the table and at least it adds the new row to the table but does not insert the table's formulas into it. So why does this happen with my other tables? is it because the formulas in my other tables include ranges running down the columns that excel recognizes and adds to this range automatically? either way, how do I get excel to do this with my current tables that have formulas that only deal with cells along the same row? Thanks

[Norie]

How did you create the 2nd table?

[juntjoo]

Just "insert"ed it on top of a row or two

[Norie]

Any chance you could upload a sample workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

[juntjoo]

436u1fgan7mj.jpg

unfortunately this is happening when I try to do that. Anyone seen this?

[juntjoo]

http://www.filefactory.com/file/32cx...st/sample.xlsx there's the file.
But now I got it to somehow add formulas under column B, this one intended to insert today's date once you enter data into the orange cells, but when you do that you don't get today but some 1900 date. So now that's my problem lol. Sorry if this is getting confusing. I hope my original issue is resolved though. I think my original formula was locked or something

[Norie]

Keep on forgetting the paperclip doesn't work anymore.

Try clicking Go Advanced, scroll down and click Manage Attachments.

[juntjoo]

here we go. Thanks!

[Norie]

When I enter data the formula in B2 is being copied down but the range for the COUNTA isn't changing as expected and the range in the original formula is changing too.

My computer just died on me so I can't give details or look further into it right now but I'll try and post something later.

[juntjoo]

ah, it's because I was inserting the data just by pasting it on top of a cell rather than pasting/typing it manually into the cell. idk why this is so and I have to double click to get in there the right way, but just pasting should accomplish the same thing. oh well.

[Norie]

When I paste data into the table the formula is being copied down.

[juntjoo]

thank you. now it's acting even more funny.

...

okay, so maybe this is how it's supposed to work: you can paste into any cell to start a new table row with auto added formulas. but for my formula to work correctly you cannot add data to the target cells, the orange ones that activate my formula, to start the new table row AND have the formula return today's date. Instead it will do "1/0/1900". so you have to start the new row by adding data to any cell OTHER than the orange target cells, THEN you can add data to them to get the B column formula to work right.

Pretty round-about way so if anyone knows of a shortcut let me know. Otherwise I'll just keep working about it.

[Norie]

I was only adding data in the first column.

[FlameRetired]

Cell B2 contains this formula ... with a circular reference.

Please Login or Register  to view this content.

I'm surprised Excel accepts it without telling you.

If this is changed how does the table behave?