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

 

[Smitty]

Welcome to the Board!

Excel MVP Zack Barresse just did a good write up on Dependent Data Validation here.

There are also some links to other good DDV resources there.

HTH,

 

[Marcelo Branco]

Hi,

Maybe this

A B C
<TABLE style="WIDTH: 157pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=209><COLGROUP><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" width=76><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #8db4e2; WIDTH: 57pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20 width=76>Attributes</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e2; WIDTH: 52pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=69>Important</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e2; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64>List</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Atrib A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Atrib B</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Atrib B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Yes</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Atrib D</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Atrib C</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Atrib G</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Atrib D</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Yes</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Atrib I</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Atrib E</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Atrib J</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Atrib F</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Atrib G</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Yes</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Atrib H</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Atrib I</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Yes</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Atrib J</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Yes</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD></TR></TBODY></TABLE>

Array-formula in C2
=IF(COUNTIF($B$2:$B$100,"Yes")>=ROWS($C$2:C2),INDEX($A$2:$A$100,SMALL(IF($B$2:$B$100="Yes",ROW($B$2:$B$100)-ROW($B$2)+1),ROWS($C$2:C2))),"")

confirmed with Ctrl+Shift+Enter
(hold down both Ctrl and Shift keys and hit Enter)

copy down till the end of your data

HTH

M.

 

[Peter_SSs]

If the 'YES' list may be very long it is possible that a lot of array formulas may impact on the performance of your sheet. (Also, some people just don't like array formulas because they forget to re-confirm with Ctrl+Shift+Enter after editing the formula ).

So here is a non-array method that uses a helper column off to the right somewhere.

H1 is stand-alone formula.
Formula in H2 is copied down to the end of your data (or beyond). You could then hide column H.
Formula in C2 copied down as far as you might ever need (possibly also the end of your data)

Adjust the formula ranges if your data may go beyond row 1000.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Attributes	Important	List					5
2	Atrib A		Atrib B					0
3	Atrib B	Yes	Atrib D					1
4	Atrib C		Atrib G					1
5	Atrib D	Yes	Atrib I					2
6	Atrib E		Atrib J					2
7	Atrib F							2
8	Atrib G	Yes						3
9	Atrib H							3
10	Atrib I	Yes						4
11	Atrib J	Yes						5
12								5
13								5
List

 

[Rick Rothstein]

Would a macro solution be of any help to you? Assuming the Attributes are in Column A, the Yeses in Column B and the Output List in Column C....

Sub FindYesesMoveAttributes()
  Columns("B").SpecialCells(xlCellTypeConstants).Offset(0, -1).Copy Range("C1")
End Sub

 

[Peter_SSs]

Rick

If a macro is to be used I think you'd have to fire your code from a Worksheet_Change event, and include code to clear col C in case the list has shrunk:

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.

 

[Rick Rothstein]

Good points Peter... thanks! Here is the event code for the requested functionality...

Private Sub Worksheet_Change(ByVal Target As Range)
  Application.EnableEvents = False
  Columns("C").ClearContents
  Columns("B").SpecialCells(xlCellTypeConstants).Offset(0, -1).Copy Range("C1")
  Application.EnableEvents = True
End Sub

To install this code, right click the name tab at the bottom of the worksheet that the attributes are on, select View Code from the pop-up menu that appears and then copy/paste the above code into the code window that opened up. That's it... go back to the worksheet and type in or delete Yes values in Column B and watch Column C update accordingly.

 

[eskim]

Thanks everyone for taking a stab at it - really appreciate the help!

I decided to implement the array function suggested by Marcelo because I wanted to keep things simple and limit the already existing rat's nest of formulas in my workbook to a minimum and also because I really haven't worked with macros. The function does exactly what I'm looking for - thanks Marcelo!

Rick, just to give it a try I pasted the code you suggested but noticed that the output list populates at the top of Column C (C1). I'm not well versed on the syntax of macros. Is there any way to have it output at a different location in Column C?

 

[Rick Rothstein]

Rick, just to give it a try I pasted the code you suggested but noticed that the output list populates at the top of Column C (C1). I'm not well versed on the syntax of macros. Is there any way to have it output at a different location in Column C?

This is the line you need to change...

Columns("B").SpecialCells(xlCellTypeConstants).Offset(0, -1).Copy Range("C1")

specifically, the "C1" at the end of it... just change the C1 to the cell address you want the first item of the list to be placed in (you are not restricted to using Column C.. any cell address that is not within the existing list can be used).

 

[Marcelo Branco]

Thanks everyone for taking a stab at it - really appreciate the help!

I decided to implement the array function suggested by Marcelo because I wanted to keep things simple and limit the already existing rat's nest of formulas in my workbook to a minimum and also because I really haven't worked with macros. The function does exactly what I'm looking for - thanks Marcelo!

You are welcome.

Tks for providing feedback for us.

M.