Self-adjusting formulas when a row is inserted into a linked sheet?

[Royzer]

Hi.

I am using Excel 2007 and I have a workbook with several sheets, each of which is used for data entry for a specific type of revenue. Each sheet has a specific number of rows available for data entry. The last sheet in the workbook is linked row by row to all the data rows in the individual sheets so as to consolidate the information so it can be posted to the general ledger all at once.

The file has worked great, but this month my boss needed to add some rows to one of the input sheets to accommodate some additional revenue. This, of course, screwed up the consolidation sheet at the end of the file because the sheet did not/could not add links and formulas for the new rows, so the additional information did not carry to the sheet. It would be great if there was some way to make the consolidation sheet insert a row and copy the formulas down from the row above it when the user inserts a row into one of the input sheets. Is there any way to do this using VBA?

Thanks!

 

[SkipVought]

hi,

This, of course, screwed up the consolidation sheet at the end of the file

FYI, this is a poor design, and the proof of that, is this very issue.
[quote[It would be great if there was some way to make the consolidation sheet insert a row and copy the formulas down from the row above it when the user inserts a row into one of the input sheets. Is there any way to do this using VBA? [/quote]
Rather than spending time and effort programming a kluge, I'd just do the manual insert and copy to fix the immediate and then design a better functioning workbook, that uses best and accepted practices. If it were me, I'd use MS Query to join all the data in the summary sheet. Would not matter how many rows you might add or remove from any sheet!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Royzer]

Point taken. Thanks, Skip.