Spreadsheet: 60370d1260917105-inserting-data-blocks-maintaining-referencing-me-test-book-2
Sheet: Sheet1

 |  |  | Area unavailable for use by cells below
 |  |  |  |  |  | (Customer alpha |  |  |  |  | Example
 |  |  |  |  |  | short code) | (User name) | (User Inits) | (Ref code) | (Init Count) | Init Count
Ref Code | 100 |  |  |  |  | abc | [NAME] | JB | 100 | #N/A | 0 |  | Price data is added manually (data block is 10 rows deep and 22 columns wide). There are typically 50 price data blocks.
 | 100 |  |  |  |  | abc | [NAME] | TG | 100 | #N/A | 0
 | 100 |  |  |  |  |  |  |  | 100 | #N/A | 0 |  | User names and initials are added manually. Data in both columns are used elsewhere in the workbook
 | 100 |  |  |  |  |  |  |  | 100 | #N/A | 0
 | 100 |  | Price data abc |  |  |  |  |  | 100 | #N/A | 0 |  | Customer alpha short code is manually copied to fill the next cell down as User names and User Inits are added
 | 100 |  |  |  |  |  |  |  | 100 | #N/A | 0
 | 100 |  |  |  |  |  |  |  | 100 | #N/A | 0 |  | Init Count indexes the number of User Inits associated with each customer. Count stops for the price block selected elshwere in $H$1
 | 100 |  |  |  |  |  |  |  | 100 | #N/A | 0
 | 100 |  |  |  |  |  |  |  | 100 | #N/A | 0 |  | Price data blocks and associated data need to be stored in alphabetical order, for use elsewhere
 | 100 |  |  |  |  |  |  |  | 100 | #N/A | 0
Ref code | 101
 | 101
 | 101
 | 101 |  | New data block here
 | 101
 | 101
 | 101
 | 101
 | 101
 | 101
Ref code | 102 |  |  |  |  | bcd | [NAME] | AB | 101 | #N/A | 0 |  | Task:
 | 102 |  |  |  |  |  |  |  | 101 | #N/A | 0 |  | When a new user occurs a new data space is created:
 | 102 |  |  |  |  |  |  |  | 101 | #N/A | 0 |  |  | Highlight data block eg. C20:K29, not columns A, B.
 | 102 |  |  |  |  |  |  |  | 101 | #N/A | 0 |  |  | Right click - Insert - Shift cells down. See example as left.
 | 102 |  |  |  |  |  |  |  | 101 | #N/A | 0
 | 102 |  |  |  |  |  |  |  | 101 | #N/A | 0 |  | Values in Col J in rows 20 and beyond need to contain the applicable ref code from Col B, once the space for the new data block has been created.
 | 102 |  | Price data bcd |  |  |  |  |  | 101 | #N/A | 0
 | 102 |  |  |  |  |  |  |  | 101 | #N/A | 0 |  | Various attempts at use of INDIRECT function to call the Ref Code from Col B have not worked.
 | 102 |  |  |  |  |  |  |  | 101 | #N/A | 0
 | 102 |  |  |  |  |  |  |  | 101 | #N/A | 0 |  | Manually copying K19 down to K30 after Shift Cells Down does copy the formula correctly (try it).
Ref code | 103 |  |  |  |  | cde | [NAME] | TJ | 102 | #N/A | 1
 | 103 |  |  |  |  | cde | [NAME] | GS | 102 | #N/A | 2 |  | The formula in Col J needs to have the same effect - ie. When copied down from J19 to J30, all rows after J30 should be automatically updated with the new Ref Code.
 | 103 |  |  |  |  | cde | [NAME] | MB | 102 | #N/A | 3
 | 103 |  |  |  |  |  |  |  | 102 | #N/A | 3 |  | The desired effect is to keep data blocks in alphabetical order whilst re-referencing all subsequent price blocks with  the revised Ref Code, thus keeping numerical order.
 | 103 |  |  |  |  |  |  |  | 102 | #N/A | 3
 | 103 |  |  |  |  |  |  |  | 102 | #N/A | 3
 | 103 |  | Price data cde |  |  |  |  |  | 102 | #N/A | 3
 | 103 |  |  |  |  |  |  |  | 102 | #N/A | 3
 | 103 |  |  |  |  |  |  |  | 102 | #N/A | 3
 | 103 |  |  |  |  |  |  |  | 102 | #N/A | 3
Ref code | 104 |  |  |  |  | def | [NAME] | JT | 103 | #N/A | 3
 | 104 |  |  |  |  |  |  |  | 103 | #N/A | 3
 | 104 |  |  |  |  |  |  |  | 103 | #N/A | 3
 | 104 |  |  |  |  |  |  |  | 103 | #N/A | 3
 | 104 |  |  |  |  |  |  |  | 103 | #N/A | 3
 | 104 |  | Price data def |  |  |  |  |  | 103 | #N/A | 3
 | 104 |  |  |  |  |  |  |  | 103 | #N/A | 3
 | 104 |  |  |  |  |  |  |  | 103 | #N/A | 3
 | 104 |  |  |  |  |  |  |  | 103 | #N/A | 3
 | 104 |  |  |  |  |  |  |  | 103 | #N/A | 3
 |  |  |  |  |  | efg | [NAME] | DC | 104 | #N/A | 3
 |  |  |  |  |  | efg | [NAME] | SH | 104 | #N/A | 3
 |  |  |  |  |  |  |  |  | 104 | #N/A | 3
 |  |  |  |  |  |  |  |  | 104 | #N/A | 3
 |  |  | Price data efg |  |  |  |  |  | 104 | #N/A | 3
 |  |  |  |  |  |  |  |  | 104 | #N/A | 3
 |  |  |  |  |  |  |  |  | 104 | #N/A | 3
 |  |  |  |  |  |  |  |  | 104 | #N/A | 3
 |  |  |  |  |  |  |  |  | 104 | #N/A | 3
 |  |  |  |  |  |  |  |  | 104 | #N/A | 3