PDA

View Full Version : Solved: Populate Column based on contents of range

DekHog
07-23-2007, 07:34 AM
Hi all...

Can anyone help with this? I tried posting it in an Excel Forum (thought it would be a formula rather than VBA) but got no replies despite many views...

The title of the post basically explains the problem, and trying to explain it here would be nigh on impossible, so I've attached the xls with the problem in a text box in it.

I've also populated two of the cells in the 'Level' column to assist.

Thanks to all...

rory
07-23-2007, 07:41 AM
It's not pretty but you can put this in F2 and copy down:

=IF(COUNTIF(\$L\$2:\$O\$10,E2)=0,"",INDEX(\$K\$2:\$K\$10,SUMPRODUCT(((\$L\$2:\$L\$10=E2)+(\$M\$2:\$M\$10=E2)+(\$N\$2:\$N\$10=E 2)+(\$O\$2:\$O\$10=E2))*(ROW(\$L\$2:\$L\$10)-1))))

HTH
Rory

rory
07-23-2007, 07:42 AM
PS That assumes there will only ever be one match in the table in L2:O10 for any entry in column E.

DekHog
07-23-2007, 11:22 PM
Thanks, it works a treat... but I wish I knew how you actually start writing a formula like that... I wouldn't even attempt to edit it, never mind write it!!