Inserting data blocks, maintaining referencing

[jbitluk]

Hi,

I've a complex pricing lookup workbook with a problematic worksheet.

The worksheet (sample attached) occasionally needs to have a new block of data inserted in the middle of the sheet.

Each block of data is referenced by other worksheets and I'm having trouble maintaining the referencing when new data is inserted. I've tried following others examples of using INDIRECT in col K, but to no avail.

A fuller description of the problem, with an example, is contained in the attached file.

A simple solution (ie commands rather than VBA please) would be gratefully welcomed.

Many thanks.

[seckert]

Shifting data is always problematic. Far cleaner simply to insert lines.

Highlight 10 lines (entire row) where you want the new data. <Ctrl><C> to copy, <Ctrl><+> to insert a copy as new rows. Now overwrite the data in the new rows with the new data, re-do your Ref Codes and you're in business. All your range references and formulas should still be in tact. (Not sure what you're trying to do with the indirect statement in col J, but I think you can just make that '=Bxx' and accomplish the same thing.