Anyone have a trick for combining information from different spreadsheets with individual layouts from separate departments while maintaining data integrity? I worry that consolidating by using 3-d formulas will become unreliable if the source sheet has a line deleted.
My favorite way is to insert an Upload or Transfer page into each File which you want to consolidate. Setup the Upload page in the format you want and then Lock the transfer page down. You can also build in checks to validate that it is collecting all the data you want as well as check for negatives, duplicates and missing data as required.
As the upload page it is in the spreadsheet you want any changes in the format inserting or deleting rows/columns will update automatically even if a user deletes rows or columns on the other pages.
You can then link or import happily knowing that the format is intact.