Real-time list function?

[eskim]

I've got toughie I've been wrestling with that I wanted to run by you guys to see if someone might have any ideas.

In Col A, I've got a long list of various attributes in alphabetical order.

In Col B, I have the user select "Yes" from a drop-down list if they think that attribute is important; if they don't deem it important, they're instructed to leave that cell blank.

What I'd like to do in Col C is to have it automatically populate with those attributes from Col A that the user selected as important in Col B but to have the list automatically truncate to the top of the column by eliminating blank rows (and also remain in alphabetical order). I'd also like for Col C to update in real time as the user goes back and makes changes to their important attributes in Col B.

• Is it even possible to do this on Excel?
• Any ideas how?

Thanks,
Steve

 

[eskim]

I'm going to ask for help on another area of this same workbook as it seems to be beyond my abilities.

• In Col A, I have the user list their significant life experiences.
• In Col B, the user enters the month & year of each experience.
• Col C automatically populates with the user's age at each experience.
• In Col D, the user selects from a drop-down list whether the experience was 'Painful' or 'Positive'. If neither painful nor positive, then the user is instructed to leave this cell blank.
• In Col E, I have the user describe, for ONLY their painful or positive experiences, their 'Life lesson(s) learned / Benefits of experience'.

For Col F, I'd like to have list in chronological order the user's descriptions from Col E for their Painful experiences, truncated to the top to eliminate blank spaces.

For Col G, I'd like to have list in chronological order the user's descriptions from Col E for their Positive experiences, truncated to the top to eliminate blank spaces.

 

[Marcelo Branco]

Hi eskim,

Could you post a sample of your data?

To do this select a relevant part of your data including headers (10/15 rows), put borders, copy (Ctrl+C) and paste (Ctrl+V) in the forum reply-page.

M.

 

[eskim]

Marcelo, I tried copying & pasting a sample of the worksheet with borders around it them, but for some reason, the post isn't keeping the formatting. Is there a way to ensure that formatting gets copied over?

 

[Marcelo Branco]

eskim,

Which browser are you using? I think the copy+paste works better with Internet Explorer

In any case, i think the forrmats are not so important. Try again, please.

M.

 

[eskim]

Table removed by Moderator at OP's request

 

[Marcelo Branco]

eskim,

Perfect!

I'll think about.

M.

 

[Peter_SSs]

Marcelo, I tried copying & pasting a sample of the worksheet with borders around it them, but for some reason, the post isn't keeping the formatting. Is there a way to ensure that formatting gets copied over?

Which browser are you using? I think the copy+paste works better with Internet Explorer

I see that you have posted a table, but for the Borders-Copy-Paste method as well as Marcelo's comment you also need to check your UserCP settings - see the blue section in this post.

 

[Marcelo Branco]

eskim,

Try this

array-formula in F2 (Painful)
=IFERROR(INDEX($E$2:$E$100,MATCH(SMALL(IF($D$2:$D$100="Painful",$B$2:$B$100),ROWS($1:1)),$B$2:$B$100,0)),"")
Ctrl+Shift+Enter

copy down as you need

array-formula in G2 (Positive)
=IFERROR(INDEX($E$2:$E$100,MATCH(SMALL(IF($D$2:$D$100="Positive",$B$2:$B$100),ROWS($1:1)),$B$2:$B$100,0)),"")
Ctrl+Shift+Enter

copy down as you need

HTH

M.

 

[Marcelo Branco]

eskim,

Results achieved

Table removed by Moderator at OP's request