# Add a Blank Row for New Value added to a List

#### nlowery12

##### New Member
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

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
@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
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
66.6 KB · Views: 3
• 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
21.7 KB · Views: 4

#### Snakehips

##### Well-known Member
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
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
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.

Replies
6
Views
138
Replies
2
Views
102
Replies
4
Views
71
Replies
1
Views
147
Replies
9
Views
208

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.

### Which adblocker are you using?

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

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