Autopopulate Data to multiple sheets while eliminating zero or blank cells

Hello Everyone,

I believe this may be the first time I have ever signed up and posted on a public forum like this; exciting! My first excel sheet is simply titled "name sheet." This is where client names are entered. I have two other sheets, with a couple more to come, that I also need to have the same client name entered into. On the other sheets I entered "='Name Sheet'!A2" This will autopopulate names as they are entered on the name sheet. I dragged this equation down about 1k cells; however I only have 80 names entered right now. The blank cells have generated a zero "0". When I go to sort the names alphabetically after entering new names, all the cells with zero in them move to the top and my names are sent to the bottom.

Did I formulate the equation wrong to auto populate the names, or is their a way to automatically sort the names while excluding the 0's. I do know when filtering I can uncheck the "0" to exclude it from being sorted, however I have to make this program as simple and user friendly as possible. Thank you all for your time.

Attached is a example of what I am dealing with.

It looks like your names are already sorted in the Data Names sheet.

In the second sheet you can do this:

=IF('Data Names'!A2="","",'Data Names'!A2)

to avoid getting zero instead of blank entries. A shorter version, which does the same thing, is:

='Data Names'!A2&""

Hope this helps.

Pete

Thanks for taking time to look it over. I tried both of your suggestions, which seem to work at first. However, as soon as I sort the names from z-a and then back to a-z, any blank cells are sorted to the top of the list; bringing right back to where I was.

Why do you need to sort the data, when it is already sorted?

Pete

That did fix the original problem. Now when I go to add client specific information with that person, anytime I add a new name on the Data name page and have that name alphabetized, the information associated with that specific person doesn't move with that person.

I can eliminate the data name sheet if that is possible and make the RHU sheet the main sheet to enter the names. The Data Names sheet was created in order to quickly enter names that would then be filed alphabetically onto the other sheets.

My goal is every time I enter a name on one sheet, that same name is also entered on all the other sheets. However each sheet is used for something different. One sheet might have personal information and another is for data purposes. I am trying to eliminate having to enter the same name on every sheet. However, whenever a name is entered, it needs to be alphabetized and the data attached to that name needs to stay with that specific person.

You shouldn't add data on the subsidiary sheets, otherwise it will get out of alignment if your names are then sorted into a different position.

Suppose you start off with Adam, David and Graham and your names are in that order on the subsidiary sheets. If you enter some data for David, it will go onto line 2. If you then add a new name, say Alan, and you want to maintain the alphabetical ordering, then David's name will move down to row 3, but the data that is on row 2 will not.

I can't think of any way using a formula to get that data (and any data for other names further down the list) to move down just because you have added the new name Alan.

Pete