Hi,

I hope someone can help me with this. Here is an example of my spreadsheet:

A B C D
1 01/07/2012 02/07/2012 03/07/2012
2 NAME
3 John H H
4 Gary H
5 Paul H H


Now what I would want to do is to be able to create a list of names for a given date where there is a H in the date cell, for example if my date was 02/07/2012 then the list will give me John and Gary and if the date was 03/07/2012 then it would only give me Paul.

I hope this makes sense.

Thanks in advance for any help.

Z

Sorry the table didnt appear as I thought it would. So here it is again (ignore the dots)

.......A..................B....................... ..C..........................D
1.................01/07/2012............02/07/2012.............03/07/2012
2 NAME
3 John................H.........................H
4 Gary...........................................H
5 Paul................H............................. .........................H

Hope it works this time

One way, either manually or via VBA, is to set AutoFilter for all the date columns. Pick a date and filter to hide blanks. Then copy the visible names in the first column and paste elsewhere.

Here is solution:

=IF(INDIRECT("R" & ROW() & "C" & MATCH($F$1,$A$1:$E$1,0),0)="H",A3,"")


Paste this formula in Cells F3 with following conditions:

- F1 should contain date from your data
- F2 empty row
- Column E is blank
- Columns A:D should contain ur data


Hereunder is sample

A B C D E F
01-07-2012 02-07-2012 03-07-2012 03-07-2012

John H H
Gary H
Paul H H Paul

For any detail ping me or visit

Here is solution:

=IF(INDIRECT("R" & ROW() & "C" & MATCH($F$1,$A$1:$E$1,0),0)="H",A3,"")


Paste this formula in Cells F3 with following conditions:

- F1 should contain date from your data
- F2 empty row
- Column E is blank
- Columns A:D should contain ur data


Hereunder is sample

A B C D E F
01-07-2012 02-07-2012 03-07-2012 03-07-2012

John H H
Gary H
Paul H H Paul

For any detail ping me or visit www.Excelolozy.com

Thanks, this might be a bit long winded for people.

Thanks for this. This should work for now, Cheers