Data lists

[stinkoman]

I am using excel 2007 and windows vista.

2 questions i need help with

1)
i have 2 colums, A and B. In colum A I have some data. in colum B i have some boolean value, yes, no or its blank. I want to be able to create a third colum, or another colum in another page from all the values in A that have a coresponding value in B.

i know how to use the vlookup function pretty well. but when i use vlookup i get NA# errors in between the values.

For example (because im not sure i know how to explain it)

A B
12 yes
23
14 yes
53 yes
14
15
16 yes

I want to take the above table and dynamically create a culum or another table with just the "yes" values. output from above should look like this:

12
14
53
16

instead i get something like

12
NA#
14
53
NA#
NA#
16

Now the boolean colum is using a list for data validation, {Yes;No;blank}.

Is it possible to create this with just the excel functions?

2)
if so, is it possible to go further and take blocks cells and create a dynamic lists based on a boolean value in one of the cells? So for every block of cells (x number of rows and colums) arranged in one book as a list of data values with coresponding boolean values, could i create a dynamic list with just the selected blocks of data with the "yes" boolean?



I know the second one is hard to understand, but i tried my best to explain it. I can get going if i can figure out how to do #1.

thanks for any help that the community may provide.

[Palmetto]

You might try using a helper column.

In C1, copied down:
=IF(B1<>"",ROW(),"")

Then in another column to get just the A-B matches:
=INDEX($A$1:$A$7,MATCH(SMALL($C$1:$C$7,ROW()),$C$1:$C$7,0))

[stanleydgromjr]

stinkoman,

Detach/open workbook Data lists - stinkoman - EF766186 - SDG10.xlsx, and check Sheet1 and Sheet2.

[stinkoman]

Thank you both for you help. that was exactly what i was looking for. Reputation awarded!

Stanley, that was a clever use of iserror.

I would like to take this further and use it accross multiple sheets. so basically i have about 4 sheets organized with data as before. i want to use the last sheet to build a list with all the selections from the previous sheets in one single colum. so basically the final sheet will start with sheet one's selections (yes's) at the top, then followed by the selections (yes's) from sheet 2 and so on..

i tried to modify what you guys compiled for me, but i kept getting hung up on how to start sheet two under the output from sheet one. it should be dynamic in the sense that you dont know how many entries from sheet one there are untill selected.

Thanks Stinko.

[stanleydgromjr]

stinkoman,

Thanks.

To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed) that contains an example of your raw data on the first four worksheets, and on another worksheet your desired results.

The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

To attach your workbook, scroll down and click on the Go Advanced button, then scroll down and click on the Manage Attachments button.

[stinkoman]

Stanley,
thanks for the insight. i have attached a sample of the sheet im trying to create. there are 3 pages. page 1 and 2 do what you helped me accomplish in the first post. that is, dynamically build a list based on selections in another list.

what i am trying to do is transpose all selections from both pages (and possibly more) to the last page, dynamically.

The stuff in red is the dynamically created stuff and the light grey is the helper material.

I have been successfull in getting the first page's list to move to the last page, but i cant figure out how to get the next page's list to move to the last page following the first one.

Again, thank you for your help.

[stanleydgromjr]

stinkoman,

In reference to your latest workbook:

I could probably create a macro to get the data from the two worksheets to the last sheet.

But, you are looking for a formula.

Please click on the New Post button, then just enter the word BUMP, and then click on the Submit Reply button, and someone else will assist you.

[stinkoman]

BUMP

Thanks!