Adding vs Deleting rows or columns

GomaPile

Active Member
Joined
Jul 24, 2006
Messages
296
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)
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Forum statistics

Threads
1,136,340
Messages
5,675,202
Members
419,553
Latest member
hanahass

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top