+ Reply to Thread
Results 1 to 12 of 12

Autopopulate Data to multiple sheets while eliminating zero or blank cells

  1. #1
    Registered User
    Join Date
    10-23-2019
    Location
    New Mexico, USA
    MS-Off Ver
    latest
    Posts
    5

    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.
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,608

    Re: Autopopulate Data to multiple sheets while eliminating zero or blank cells

    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

  3. #3
    Registered User
    Join Date
    10-23-2019
    Location
    New Mexico, USA
    MS-Off Ver
    latest
    Posts
    5

    Re: Autopopulate Data to multiple sheets while eliminating zero or blank cells

    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.

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,608

    Re: Autopopulate Data to multiple sheets while eliminating zero or blank cells

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

    Pete

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,104

    Re: Autopopulate Data to multiple sheets while eliminating zero or blank cells

    Convert Data Names into a table.

    In RHU.....

    =IFERROR(Table3[@[Inmate Names]],"")

    copy down until you see blanks... or further...
    Attached Files Attached Files
    Glenn



  6. #6
    Registered User
    Join Date
    10-23-2019
    Location
    New Mexico, USA
    MS-Off Ver
    latest
    Posts
    5

    Re: Autopopulate Data to multiple sheets while eliminating zero or blank cells

    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.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    10-23-2019
    Location
    New Mexico, USA
    MS-Off Ver
    latest
    Posts
    5

    Re: Autopopulate Data to multiple sheets while eliminating zero or blank cells

    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.

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,104

    Re: Autopopulate Data to multiple sheets while eliminating zero or blank cells

    Download, look at and amend (RHU col A) the sample you just posted. It has external links in it. Please remove them as we can't do anything with them there.

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,104

    Re: Autopopulate Data to multiple sheets while eliminating zero or blank cells

    Also, I'm a bit confused by sheet 3, which I missed last time (sorry!!). Which sheet(s) do you want data to come from? Which sheet(s) do you want data to go to??

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,104

    Re: Autopopulate Data to multiple sheets while eliminating zero or blank cells

    I think you need to step back for a moment and take a deep breath. We really need to see what you are starting with... what you want to do and your desired result (mocked up). There are RHUs and Yes/Nos in various places and I haven't a clue what they are/where they came from/what I'm meant to do with them.

    Please remember that you've been looking at this stuff for minutes/hours (!!) whereas I've seen it for a minute at most and don't know what's inside your head...

  11. #11
    Registered User
    Join Date
    10-23-2019
    Location
    New Mexico, USA
    MS-Off Ver
    latest
    Posts
    5

    Re: Autopopulate Data to multiple sheets while eliminating zero or blank cells

    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.
    Attached Files Attached Files

  12. #12
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,608

    Re: Autopopulate Data to multiple sheets while eliminating zero or blank cells

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Eliminating the blank cells
    By thilag in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-23-2015, 01:23 AM
  2. Autopopulate data into different sheets
    By spwho in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-27-2014, 09:50 AM
  3. Autopopulate other data sheets from a master sheet
    By lorinda in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-20-2013, 04:04 PM
  4. Autopopulate multiple data sheets
    By jafox in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-14-2013, 11:56 AM
  5. Replies: 8
    Last Post: 06-30-2009, 05:32 AM
  6. Eliminating blank cells in a list on a ROW
    By grime in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-03-2005, 01:45 PM
  7. Replies: 3
    Last Post: 11-03-2005, 08:40 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1