Add a Blank Row for New Value added to a List

nlowery12

New Member
Joined
Dec 6, 2020
Messages
4
Office Version
  1. 2011
Platform
  1. MacOS
I have a List on Names on Sheet A, and the Same list (=Sheet A! A1) on Sheet B. In Sheet B and several other sheets in the workbook there are corresponding values on the same row in every sheet based on the list in Sheet A. The list is in Alphabetical order and I want to add a name to the list and in every other sheet have a blank row added with the new name. For Example Sheet A, A1 = Doe, Jane, A2 = Doe, John. I want to add a Insert a Cell between the two and make it, A1 = Doe, Jane, A2 = Doe, Jim, A3 = Doe, John. When the new name is inserted, I need Sheet B, A2 = Doe, Jim, but I need B2:2 to be Blank and the values that were in the same rows with Doe, Jane and Doe, John to remain the Same.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,361
Office Version
  1. 2013
Platform
  1. Windows
@nlowery12
Does it help if you substitute formula in B =SheetA! A1 with =INDEX(Sheet1!A:A,ROWS(A$1:A1),1)
Then drag down and across as required.
 

nlowery12

New Member
Joined
Dec 6, 2020
Messages
4
Office Version
  1. 2011
Platform
  1. MacOS
The Index Formula Solves the problem of names not getting automatically added, which I have went around that by using OFFSET on sheet A (I add in column D, the Offset is in column A in one on the pictures below), Is Index a better way of doing this (Faster or Easier) as this is a large workbook and lots of constantly changing names and numbers?

If I drag that across it removes the numbers on the sheet. I have 8 sheets like the one pictured below, all with different numbers on them, and a lot more names than this example. I need the The Blank Row to be added in between the two and move all corresponding numbers with the names they were associated with. I am trying to avoid manually inserting a row to multiple sheets every time I add a name.
 

Attachments

  • Screen Shot 2021-05-18 at 6.55.22 AM.png
    Screen Shot 2021-05-18 at 6.55.22 AM.png
    66.6 KB · Views: 3
  • Screen Shot 2021-05-18 at 6.59.40 AM.png
    Screen Shot 2021-05-18 at 6.59.40 AM.png
    43.5 KB · Views: 2
  • Screen Shot 2021-05-18 at 7.05.58 AM.png
    Screen Shot 2021-05-18 at 7.05.58 AM.png
    21.7 KB · Views: 4

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,361
Office Version
  1. 2013
Platform
  1. Windows
If you are using OFFSET function of a large dataset then you are compromising worksheet performance. Offset is a 'Volotile' function and triggers a recalculation every time there is a change of data within the worksheet. It's your call whether it's use is acceptable in your situation.
I get the fact that you are inserting a new name / blank row in sheet A.
Doe it help to select the range of 8 or whatever tabs, using **** Enter and then Insert Row and Enter Name?
All sheets will then have inseted row and name, for the price of one.
Otherwise, for me, the images you are posting are not really giving much useful information.
 

nlowery12

New Member
Joined
Dec 6, 2020
Messages
4
Office Version
  1. 2011
Platform
  1. MacOS
Selecting Multiple Tabs Does Solve the Issue, however I'm trying to find a way for it to automatically happen when added to the List of names on Sheet A, in order to make it more user friendly for someone who doesn't use excel much. I looked for similar VBA codes, but only found ones that would insert into every worksheet, but not select ones.
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,361
Office Version
  1. 2013
Platform
  1. Windows
Maybe this approach?
Password Protect the various sheets but, tick all boxes bar 'Insert Rows' and 'Delete Rows'. So user can do all but Insert / delete rows.
Then in Sheet"A", have a button to " Insert Name". and have it coded to have user enter a new name into an input box. Then insert name in a new row in each of the applicable sheets.
 

Forum statistics

Threads
1,141,611
Messages
5,707,383
Members
421,508
Latest member
Jalayne

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