Excel 2010: Auto-copy formulas when adding new rows to tables

Hello:

I need Excel to auto-copy formulas when new rows are added to a table. I understand that Excel will do this as long as a number of consecutive rows before the new row have the same formula. My problem is that the formula I want to be copied down actually returns blank if a set of criteria is not met, and the user is expected to overwrite the cell with new data. Therefore, the consecutive row requirement may not be met and when a new row is added, the formula will not be copied down.

For example, cell G4:
=IF(IFERROR(VLOOKUP($A4,$A$2:G3,7,FALSE),0)=0,"",VLOOKUP($A4,$A$2:G3,7,FALSE))

It looks to see if the number in A4 has been used before. If it has, it returns the contents from the cell G in that row. If it hasn't, it returns blank, and the user is supposed to enter in new content.

Is it possible to "force" Excel to auto copy a formula when a new row is added? Any other ideas?

Thank you.

Adam

Thank you. This actually applies to 9 separate columns, so I'd have to add 9 columns. Is there a way to do this without adding so many new columns?

I'm attaching the spreadsheet to this message.

Thanks again.

Adam

Originally Posted by DonkeyOte

Adam, I'd suggest using a separate column in your Table for manual entry and then revise your formula:

Please Login or Register  to view this content.

where Z is the manual entry column (modify as nec. of course) - it would only be used where the VLOOKUP fails.

Given this column now becomes formulae only it should auto copy.

In thinking about it more, I suppose I could create a new table in another worksheet where all new client demographic data would be inputted and stored, and then this table would be for managing the other statistics. That way, even rows for new clients would have formulas because it'd have to look up data from elsewhere.