+ Reply to Thread
Results 1 to 8 of 8

Excel 2007 : How do I remove "empty" cells in a range?

  1. #1
    Registered User
    Join Date
    08-18-2011
    Location
    East Sussex, England
    MS-Off Ver
    Excel 2007
    Posts
    76

    How do I remove "empty" cells in a range?

    I have tried to look for a solution to this but as of yet nothing seems to work.

    I've attached an example of what I am trying to get excel to do.

    In sheet2 I have a list which links to sheet1. I need some way of removing the "blank" cells in each of the column ranges in sheet2 so that for column A, B, C I only have the relevant data rather than blanks. However the cells are not blank as they all contain a formula. As such using the ISBLANK formula doesn't help. I can create an IF function to return TRUE or FALSE but then I am not sure how to use that to remove blanks.

    The reason for doing so is so that I can then create each column as a dynamic range for data validation lists in another sheet. So that in another sheet I can select A, B, or C and then have a drop down menu that has only those relevant ranges rather than including all the blanks.
    Attached Files Attached Files
    Last edited by graeme27uk; 08-23-2011 at 12:38 PM.

  2. #2
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: How do I remove "empty" cells in a range?

    Try this
    In Sheet2 A2
    Please Login or Register  to view this content.
    Drag Across to Column C then Down as required.

    This will return lists with no blanks at the beginning. (the numbers matched need not be just 1 to 3, just no duplicates in any one column in Sheet1).

    Then in the Names manager, this dynamic formula.
    Name "A"
    Refers To:=
    Please Login or Register  to view this content.
    Similarly for "B" & "C"

    Hope this helps
    Attached Files Attached Files
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  3. #3
    Registered User
    Join Date
    08-18-2011
    Location
    East Sussex, England
    MS-Off Ver
    Excel 2007
    Posts
    76

    Re: How do I remove "empty" cells in a range?

    That's great! It works for the example, but not for the real thing... which is odd.

    I'll attach the actual spreadsheet and talk through what it should do.

    )
    On the sheet "Plays For" I have selected out using a simple IF function the players/positions that play for each team. However this creates lots of blanks. How do I do it so that I can get rid of the blanks? I know I can use the filter funtion but the reason for asking is so that I can then use a dynamic range in the Team Roster (TR) sheet so that they are team specific drop down lists. If I do this currently then it includes lots of blanks as well.

    So the final outcome should be thus:

    A coach selects a race from the drop down list on the "TR 1" sheet. From that, excel then identifies from the "Plays for" sheet the players that can play for that race. The coach can then select again on the "TR 1" sheet using drop down lists in the "position" column for each numbered player what position they would put there.

    I have it in the past so that it simply includes ALL the positions regardless of race; which is ok but not so jazzy.

    So for example; Coach wants to play an Amazon team. They select Amazon from the race drop down menu. They then decide that they want Player 1 in their team to be an Amazon Linewoman. So in the Player 1 Position cell there is a drop down menu to select what position they would play in; but rather than just listing all positions for all races, it is amazon specific.

    2)
    Star players are special characters in the game - super good players. I'd like it so that if the coach wants a star player on their team that they can select in the "position" cell "star player" and then this brings up a list of the available star players. There is star player data on the "Player Data" sheet. Column A on that sheet denotes whether they are simply a Player or a Star Player.

    3)
    Is there any neater way of doing the skills for each player? The "Player Skills" sheet, Column B is a big concatenate function. I could combine this with Vlookup functions but it gets too big. I want it so that I can easily update skills for players without having to type skills in.


    EDIT: I've attached "example2.xls" as this is the data that I have to sort. I don't understand why the formula given doesn't work with this data, it works perfectly with the "example1.xls" data.
    Attached Files Attached Files
    Last edited by graeme27uk; 08-19-2011 at 02:18 PM.

  4. #4
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: How do I remove "empty" cells in a range?

    Okay, perhaps the simplest way to achieve this is to add a helper table.
    I have added this to your sheet "Player Data" Columns C:AL and used a grouping button to hide the table.

    1/. In C4
    Please Login or Register  to view this content.
    Drag Across to Column AL then down to suit.

    2/. In Sheet "Plays for"
    In Column A I have put index numbers they are not really required but do serve as a visual indicator as to the row where the following formula ends
    In B2
    Please Login or Register  to view this content.
    Drag Across to Column AE then down to the last index number.
    If you don't want this column then use this formula instead
    Please Login or Register  to view this content.
    "Players" is a dynamic named range
    Refers to:=
    Please Login or Register  to view this content.
    3/. As you will have some 30 names to add this code will do it for you.
    Please Login or Register  to view this content.
    4/. Unfortunatly Data Validation using =INDIRECT() doesn't like refering to dynamic names so we will need to use the Worksheet change event to change the lists as required
    In the worhsheet module for Sheet "TR 1"
    Please Login or Register  to view this content.
    Check this out by selecting a name in Sheet "TR 1" and see the validation lists in C3:18

    5/. Regarding the concatenation problem.
    As we need VBa to get your drop-downs we may as well use a UDF to do the concatination.
    Please Login or Register  to view this content.
    Then in Sheet "Player Skills" B3
    Please Login or Register  to view this content.
    Drag/Fill Down as required

    Note
    I have removed the commas from your data and used ", " as the separator in the above UDF
    The separator is optional if omitted it will use " " (space).
    Use whatever you want as a separator e.g. " & " or " and " etc.

    Lastly I have added some named ranges to make life a little easier
    TeamNames
    Refers to:=
    Please Login or Register  to view this content.
    If you have more teams to add put them in Sheet 'Player Data' BQ:BU

    In Sheet "Rerolls" A1
    Please Login or Register  to view this content.
    Drag/Fill Down as required

    This is now used to get the named list "Team"

    Hope this helps
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-18-2011
    Location
    East Sussex, England
    MS-Off Ver
    Excel 2007
    Posts
    76

    Re: How do I remove "empty" cells in a range?

    I've attached an updated workbook. However there are some things that I would like to do but have not got the expertise to do so.

    1)
    Would it be possible to remove the need for the sheet "Player Skills" so that the "Player Data" sheet can concatenate the skills directly. The end result being that all I would have to do is put an "x" in the appropriate skill column and it returns the concatenated skills for that player with a ", " between each skill.

    2)
    On the "TR1" sheet I would like it to be able to distinguish between star players and normal players. "Player Data" has in column A whether they are a Star Player or Player. The end result would ideally be that I can use a drop down list in C3:C18 as it does so at the moment but also have it so that I can select star player. Then from that in cells B3:B18 I would like it to give a list of available star players for that race. However I will still need to be able to enter a text name if the position is not a star player.

    3)
    Also on the "TR1" sheet. If a player gains a new skill then I would like to be able to add the skill to the relevant cell (I3:I18). Still separated with a ", ". The idea being a bit like the way skills are done so that i can just put an "x" in the relevant column for that player and the skill is added to their pre-existing skills. However, this needs to be player specifc rather than generic.

    4)
    In column S3:S18 I have a data validation of "Yes" or "No". If a Yes is entered then I would like it to add "Loner" to the skill list. It does so already but it also shows the ", " all the time.

    5)
    I will need to also create about 20 new sheets that are exactly the same as "TR1" so that eventually I end up with TR20 or however many I need. As such I need each "TRx" sheet to be independant of each other.


    Thanks for any help.
    Attached Files Attached Files

  6. #6
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: How do I remove "empty" cells in a range?

    Hmm?

    Let's do this step at a time this should help you to better understand how things work.

    With Sheet "Plays for"
    1/. Unhide Column A and extend the series numbers from 1 to 10, to 1 to 20 (you can hide this again once this is done.)

    2/. Select B10 and drag Down to series number 20 row then Across to Column AA ("Bretonnian")

    You should now see that several lists have more than 10 entries.

    The series number should preferably end with the maximum number of players you can have, or reasonably expect to have, in the longest range.

    The dynamic names will handle this situation so now if you go to Sheet "TR 1"
    Change the selection in B1 to say "Orc" the drop-downs in C3:C18 will now have all 13 players.

    Questions
    1/. Do you need to clear any previously selected players from C3:C18 when B1 changes?

    2/. With Sheet "Player Data" I see that you have changed the "x"s to numbers, in Columns AS to BW.
    The formulae in the hidden columns N:AR is designed to only count numeric values, should it also detect text entries?
    Last edited by Marcol; 08-22-2011 at 09:34 AM. Reason: Further question added before OP replied.

  7. #7
    Registered User
    Join Date
    08-18-2011
    Location
    East Sussex, England
    MS-Off Ver
    Excel 2007
    Posts
    76

    Re: How do I remove "empty" cells in a range?

    Quote Originally Posted by Marcol View Post
    Hmm?

    Let's do this step at a time this should help you to better understand how things work.

    With Sheet "Plays for"
    1/. Unhide Column A and extend the series numbers from 1 to 10, to 1 to 20 (you can hide this again once this is done.)

    2/. Select B10 and drag Down to series number 20 row then Across to Column AA ("Bretonnian")

    You should now see that several lists have more than 10 entries.

    The series number should preferably end with the maximum number of players you can have, or reasonably expect to have, in the longest range.

    The dynamic names will handle this situation so now if you go to Sheet "TR 1"
    Change the selection in B1 to say "Orc" the drop-downs in C3:C18 will now have all 13 players.
    Yes this works fine however I now have TR 1 - 10 and it only references cell B1 in sheet TR 1. I need it to be independent so that each TR can be different.

    Questions
    1/. Do you need to clear any previously selected players from C3:C18 when B1 changes?
    It doesn't need to clear players but it would be a nice feature. What is more important is that each TR sheet has the ability to be a different race so that they are independent.

    2/. With Sheet "Player Data" I see that you have changed the "x"s to numbers, in Columns AS to BW.
    The formulae in the hidden columns N:AR is designed to only count numeric values, should it also detect text entries?
    It doesn't matter. It works now with the numbers equally as well. Each star player is unique so a team can only ever take 1.

  8. #8
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: How do I remove "empty" cells in a range?

    Okay.
    Have a look at this workbook. This removes the need for the sheet "Players Skills" and uses the "x" in your table to get the correct skills.

    I have added another UDF
    Please Login or Register  to view this content.

    You can use this in Sheet "Player Data" in H4
    Please Login or Register  to view this content.
    Syntax
    =ConcatRowIf(the range to concatenate,the row number where the headers are,the flag to indicate cells to include, Optional:=the separator)

    If this suits your needs so far, mark this thread [SOLVED]

    Then I will look at your new thread and continue there.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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