Adding vs Deleting rows or columns

[GomaPile]

Using Excel 2010 - Adding vs Deleting rows or columns from any original data.

My apologies for information overload and not provide you with a proper excel diagram, I'm using my Samsung Tab A. Lets begin by starting off in cell C10 to H10 with info inside each cell across & down. Note: I had purposely left out row 'C Smith' section A, for this example; ('G Smith' section B, I will explain this part below).

After inputting raw data into your excel spreadsheet, as you normally do, then copy those cells & values to another location using (=) pointing each cell from the Original Data in C22 to H22; which is simple and easy to do.

What I like to see happen in a perfect world, how can we preform in real-time when changes are made to the original data. Can those copied linked cells below within the same worksheet of [sheet1]; or your personal preferences in another worksheet [sheet2] and not having to use vba (if this can be avoided. Though I might be forced to use vba).


Original Data
C10 has weekdays - D10 Mon; E10 Tue; F10 Wed; G10 Thu; H10 Fri.
C11 - H11 merged cells with the wording - Shift Times
C12 - has Names
C13 - A Smith (with shift-times)
C14 - B Smith (with shift-times)
[Section A: inserted row 'C Smith']
C15 - D Smith (with shift-times)
C16 - E Smith (with shift-times)
C17 - G Smith (with shift-times) [Section B: deleted row 'G Smith']
C18 - F Smith (with shift-times)

Copied cells from the Original Data in C22 to H22 across & down.

Section A: (insert a row - C Smith) when we observe the copied cells below nothing visually happens after adding an extra row called C Smith from the original data. Yes, we can see the cells the reference have changed going up in increments but we would like to see C Smith appearing in the list in real-time.

=C10; =D10; =E10; =F10; =G10; =H10 etc etc...
=C13 A Smith;
=C14 B Smith;
=? added row - 'C Smith'
=C16 D Smith;
=C17 E Smith;
=C18 G Smith;
=C19 F Smith;

Section B: (delete a row - G Smith) we do understand the functionality and the concept how Excel behaves; I totally get it. The #REF! error is caused by a deleted rows or columns. When you use explicit cell references like this (where you reference each cell individually, separated by a comma) and delete a referenced row or column, Excel can’t resolve it, so it returns the #REF! error. This is the primary reason why using explicit cell references in functions is not recommended.

Same again...we would like to see G Smith disappearing in the list in real-time. And adjusting those cell references.

=C10; =D10; =E10; =F10; =G10; =H10 etc etc...
=C13 A Smith;
=C14 B Smith;
=C15 D Smith;
=C16 E Smith;
=#REF! deleted row - C17 'G Smith'
=C17 F Smith;

Do we use either OFFSET or DEFINED NAME RANGE to get the results when Adding vs Deleting rows - Is there a sequence of steps that you must use to make this work in real-time for adjust those copied linked cells to equalling the axact info above when changes do occur from time to time. Could someone kindly show us how.

Any idea how this can be done, please let me know because Im building a Roster planner for my department and we're constantly changing daily, when staffs are upgraded from part time to full time hours.

Thanks
Goma (NASA)