Excel 2007 : 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.

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

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.

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

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?

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.