collecting information from a table without blanks

[ilan buskila]

Hi,
I have excel file with two sheets.
In sheet2 there is a table with different columns. In each column there is data and some blank cells.
In sheet1 I would like to choose name of one column from the sheet2 and then I want to get the information from this column but without the blanks.
Is there a way to do so using excel formulas, or should I look for a solution using VBA?
Attached a sample file.


Thanks.
Ilan.

[Glenn Kennedy]

I'm no sure what is meant to represent row and column headers and what is meant to represent actual cell entries. Please repost your attachemnt showing the data in their actual positions on sheets 1 and 2. Ok, You'll only be able to do that on Sheet 1 for EITHER A or B, it doesn't matter which - but do it for one of them. But it'll take a HUGE amount of pointless guesswork out of your request.

[hoyasaxa215]

It's not quite clear where your data is coming from. In Sheet 1 when you choose the letter A, the number 2 populates letter 'b' (Cell E7). However, on Sheet 2, Column A is blank for the number 2 (Cell J7). Other issues as well, such as row A on Sheet 2 doesn't contain the letter 'f', however you have it populating for the number 7 (Cell E11). Can you provide some clarity as to how this data is connected?

[hoyasaxa215]

Here's a simple template for removing blanks. Perhaps this will help.

[ilan buskila]

OK,
I can see now that I was not clear,
I think that the attached file is better.
Let's say that I choose TYPE 1 in cell A1 in Sheet1.
What I actually need is that the excel will do is HLOOKUP of TYPE 1 in Sheet2 and bring back all the data in TYPE 1 column back to columnB in Sheet1 only cells that contains information (none blank).
I hope that this is more clear.
Thank you for the quick reply.
Ilan.

[Vikas_Gautam]

Hi,
I have done this by using named ranges.
Just create Named Ranges as below:-
Table

Please Login or Register  to view this content.

Col

Please Login or Register  to view this content.

RowArr

Please Login or Register  to view this content.

and Use the following formula anywhere to list the non blank cells.

Please Login or Register  to view this content.

Change the Table Dimensions Manually.

To add named ranges, Goto Formula ---> Name Manager.
Edit:- Changed as per new requirements
Check the attached file:-

[newdoverman]

I selected your little table with the headings A B C D and clicked on the Formula Tab, Create From Selection and chose Top Row. This created names A B C_ D. In order to be able to use all those names, I created a VLOOKUP table in P5:Q8 to translate the value in C6 to the names created. I used those range names in the formula. You can enter any value from A to D and have the correct values returned.

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Here is your file with the formula implemented.....Area with red border.

[ilan buskila]

Hi Guys,
All your examples are working great!!
Thank you very mutch.
Ilan.