I have a list of Dates in Col. A
Column B contains both numerical and text values.
I need to define a value in column B, and create a list of the dates
that these occured on, on another sheet. Auto filter doesn't work
because there are several different columns. If I try to use it I also
get the values in the other columns.


A B
1/2 8
1/3 4
1/4 Vac
1/5 8
1/6 7
1/7 Vac
1/8 8



Value needed = Vac

Solution 1/4
1/7

Thanks for any and all help.

Hi!

Entered as an array with the key combo of CTRL,SHIFT,ENTER:

=IF(ROWS($1:1)<=COUNTIF(Sheet1!B$2:B$8,"vac"),INDE X(Sheet1!A$2:A$8,SMALL(IF(Sheet1!B$2:B$8="vac",ROW (Sheet1!A$2:A$8)-ROW(Sheet1!A$2)+1),ROWS($1:1))),"")

Copy down until you get blanks.

Biff

"smoore" wrote in message
oups.com...
I have a list of Dates in Col. A
Column B contains both numerical and text values.
I need to define a value in column B, and create a list of the dates
that these occured on, on another sheet. Auto filter doesn't work
because there are several different columns. If I try to use it I also
get the values in the other columns.


A B
1/2 8
1/3 4
1/4 Vac
1/5 8
1/6 7
1/7 Vac
1/8 8



Value needed = Vac

Solution 1/4
1/7

Thanks for any and all help.

P.S.

Format the cells as DATE

Biff

"Biff" wrote in message
...
Hi!

Entered as an array with the key combo of CTRL,SHIFT,ENTER:

=IF(ROWS($1:1)<=COUNTIF(Sheet1!B$2:B$8,"vac"),INDE X(Sheet1!A$2:A$8,SMALL(IF(Sheet1!B$2:B$8="vac",ROW (Sheet1!A$2:A$8)-ROW(Sheet1!A$2)+1),ROWS($1:1))),"")

Copy down until you get blanks.

Biff

"smoore" wrote in message
oups.com...
I have a list of Dates in Col. A
Column B contains both numerical and text values.
I need to define a value in column B, and create a list of the dates
that these occured on, on another sheet. Auto filter doesn't work
because there are several different columns. If I try to use it I also
get the values in the other columns.


A B
1/2 8
1/3 4
1/4 Vac
1/5 8
1/6 7
1/7 Vac
1/8 8



Value needed = Vac

Solution 1/4
1/7

Thanks for any and all help.

Biff, Thanks for trying to help me. I've been scratching various
parts of my anatomy for some time over this. I'm glad someone has
finally come up with a solution.
Now need a little more help with the tweaking to get it to work in my
real worksheet. I've entered the following function in a sheet called
"Summary", all my data is in a sheet called "Attendance" . You will see
I've attempted to rewrite your function for this , but I'm still not
getting it right. Can you steer a rookie a little further? Thanks.

IF(ROWS($1:1)<=COUNTIF(Attendance!D$3:D$317,"Vac") ,Index(Attendance!B$3:B$317,SMALL(IF(Attendance!D$ 3:D$317="Vac",ROW(Attendance!A$2:A$8)-ROW(Attendance!A$2)+1),ROWS($1:1)))"")

When I go to implement this it returns an error and highlights the
double quotation marks at the very end.

smoore,

looks like it wants a comma before " ".

Beege

"smoore" wrote in message
oups.com...
Biff, Thanks for trying to help me. I've been scratching various
parts of my anatomy for some time over this. I'm glad someone has
finally come up with a solution.
Now need a little more help with the tweaking to get it to work in my
real worksheet. I've entered the following function in a sheet called
"Summary", all my data is in a sheet called "Attendance" . You will see
I've attempted to rewrite your function for this , but I'm still not
getting it right. Can you steer a rookie a little further? Thanks.

IF(ROWS($1:1)<=COUNTIF(Attendance!D$3:D$317,"Vac") ,Index(Attendance!B$3:B$317,SMALL(IF(Attendance!D$ 3:D$317="Vac",ROW(Attendance!A$2:A$8)-ROW(Attendance!A$2)+1),ROWS($1:1)))"")

When I go to implement this it returns an error and highlights the
double quotation marks at the very end.

Beege, your absolutely right, I'd just missed it. Thank you!

Biff, your a miracle worker! I would have never come up with this
solution on my own. Thank you again!

Good eyes! I didn't see that!

Also, be sure to see my other follow-up about changing the ROW() references.

Biff

"Beege" wrote in message
...
smoore,

looks like it wants a comma before " ".

Beege

"smoore" wrote in message
oups.com...
Biff, Thanks for trying to help me. I've been scratching various
parts of my anatomy for some time over this. I'm glad someone has
finally come up with a solution.
Now need a little more help with the tweaking to get it to work in my
real worksheet. I've entered the following function in a sheet called
"Summary", all my data is in a sheet called "Attendance" . You will see
I've attempted to rewrite your function for this , but I'm still not
getting it right. Can you steer a rookie a little further? Thanks.

IF(ROWS($1:1)<=COUNTIF(Attendance!D$3:D$317,"Vac") ,Index(Attendance!B$3:B$317,SMALL(IF(Attendance!D$ 3:D$317="Vac",ROW(Attendance!A$2:A$8)-ROW(Attendance!A$2)+1),ROWS($1:1)))"")

When I go to implement this it returns an error and highlights the
double quotation marks at the very end.

Hi!

Change this portion:

ROW(Attendance!A$2:A$8)-ROW(Attendance!A$2)

To be that of your actual range:

ROW(Attendance!B$3:B$317)-ROW(Attendance!B$3)

And, make sure you enter the formula as an array. That means, instead of
just hitting the ENTER key you MUST use the 3 key combination of
CTRL,SHIFT,ENTER. Hold down both the CTRL key and the SHIFT key then hit
ENTER. When done properly Excel will place squiggly braces { } around the
formula. Also, if you edit the formula it must be re-entered as an array.

Biff

"smoore" wrote in message
oups.com...
Biff, Thanks for trying to help me. I've been scratching various
parts of my anatomy for some time over this. I'm glad someone has
finally come up with a solution.
Now need a little more help with the tweaking to get it to work in my
real worksheet. I've entered the following function in a sheet called
"Summary", all my data is in a sheet called "Attendance" . You will see
I've attempted to rewrite your function for this , but I'm still not
getting it right. Can you steer a rookie a little further? Thanks.

IF(ROWS($1:1)<=COUNTIF(Attendance!D$3:D$317,"Vac") ,Index(Attendance!B$3:B$317,SMALL(IF(Attendance!D$ 3:D$317="Vac",ROW(Attendance!A$2:A$8)-ROW(Attendance!A$2)+1),ROWS($1:1)))"")

When I go to implement this it returns an error and highlights the
double quotation marks at the very end.

Biff, after I had posted my rework, i noticed the same section of the
function you caught. It's working like a chwrm. If you have the time
can you give me a quick synopsis of how these functions interact. On
there own merit I can figure them out, but I haven't been able to wrap
my ittle mind around what they cause each other to do. Thanks very much
again.