software

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)lt;=COUNTIF(Sheet1!B$2:B$8,quot;vacquot;),INDE X(Sheet1!A$2:A$8,SMALL(IF(Sheet1!B$2:B$8=quot;vacquot;,ROW (Sheet1!A$2:A$8)-ROW(Sheet1!A$2) 1),ROWS($1:1))),quot;quot;)

Copy down until you get blanks.

Biff

quot;smoorequot; gt; wrote in message oups.com...
gt;I have a list of Dates in Col. A
gt; Column B contains both numerical and text values.
gt; I need to define a value in column B, and create a list of the dates
gt; that these occured on, on another sheet. Auto filter doesn't work
gt; because there are several different columns. If I try to use it I also
gt; get the values in the other columns.
gt;
gt;
gt; A B
gt; 1/2 8
gt; 1/3 4
gt; 1/4 Vac
gt; 1/5 8
gt; 1/6 7
gt; 1/7 Vac
gt; 1/8 8
gt;
gt;
gt;
gt; Value needed = Vac
gt;
gt; Solution 1/4
gt; 1/7
gt;
gt; Thanks for any and all help.
gt;
P.S.

Format the cells as DATE

Biff

quot;Biffquot; gt; wrote in message
...
gt; Hi!
gt;
gt; Entered as an array with the key combo of CTRL,SHIFT,ENTER:
gt;
gt; =IF(ROWS($1:1)lt;=COUNTIF(Sheet1!B$2:B$8,quot;vacquot;),INDE X(Sheet1!A$2:A$8,SMALL(IF(Sheet1!B$2:B$8=quot;vacquot;,ROW (Sheet1!A$2:A$8)-ROW(Sheet1!A$2) 1),ROWS($1:1))),quot;quot;)
gt;
gt; Copy down until you get blanks.
gt;
gt; Biff
gt;
gt; quot;smoorequot; gt; wrote in message
gt; oups.com...
gt;gt;I have a list of Dates in Col. A
gt;gt; Column B contains both numerical and text values.
gt;gt; I need to define a value in column B, and create a list of the dates
gt;gt; that these occured on, on another sheet. Auto filter doesn't work
gt;gt; because there are several different columns. If I try to use it I also
gt;gt; get the values in the other columns.
gt;gt;
gt;gt;
gt;gt; A B
gt;gt; 1/2 8
gt;gt; 1/3 4
gt;gt; 1/4 Vac
gt;gt; 1/5 8
gt;gt; 1/6 7
gt;gt; 1/7 Vac
gt;gt; 1/8 8
gt;gt;
gt;gt;
gt;gt;
gt;gt; Value needed = Vac
gt;gt;
gt;gt; Solution 1/4
gt;gt; 1/7
gt;gt;
gt;gt; Thanks for any and all help.
gt;gt;
gt;
gt;
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
quot;Summaryquot;, all my data is in a sheet called quot;Attendancequot; . 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)lt;=COUNTIF(Attendance!D$3$317,quot;Vacquot;) ,Index(Attendance!B$3:B$317,SMALL(IF(Attendance!D$ 3$317=quot;Vacquot;,ROW(Attendance!A$2:A$8)-ROW(Attendance!A$2) 1),ROWS($1:1)))quot;quot;)

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 quot; quot;.

Beege

quot;smoorequot; gt; wrote in message oups.com...
gt; Biff, Thanks for trying to help me. I've been scratching various
gt; parts of my anatomy for some time over this. I'm glad someone has
gt; finally come up with a solution.
gt; Now need a little more help with the tweaking to get it to work in my
gt; real worksheet. I've entered the following function in a sheet called
gt; quot;Summaryquot;, all my data is in a sheet called quot;Attendancequot; . You will see
gt; I've attempted to rewrite your function for this , but I'm still not
gt; getting it right. Can you steer a rookie a little further? Thanks.
gt;
gt; IF(ROWS($1:1)lt;=COUNTIF(Attendance!D$3$317,quot;Vacquot;) ,Index(Attendance!B$3:B$317,SMALL(IF(Attendance!D$ 3$317=quot;Vacquot;,ROW(Attendance!A$2:A$8)-ROW(Attendance!A$2) 1),ROWS($1:1)))quot;quot;)
gt;
gt; When I go to implement this it returns an error and highlights the
gt; double quotation marks at the very end.
gt;
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

quot;smoorequot; gt; wrote in message oups.com...
gt; Biff, Thanks for trying to help me. I've been scratching various
gt; parts of my anatomy for some time over this. I'm glad someone has
gt; finally come up with a solution.
gt; Now need a little more help with the tweaking to get it to work in my
gt; real worksheet. I've entered the following function in a sheet called
gt; quot;Summaryquot;, all my data is in a sheet called quot;Attendancequot; . You will see
gt; I've attempted to rewrite your function for this , but I'm still not
gt; getting it right. Can you steer a rookie a little further? Thanks.
gt;
gt; IF(ROWS($1:1)lt;=COUNTIF(Attendance!D$3$317,quot;Vacquot;) ,Index(Attendance!B$3:B$317,SMALL(IF(Attendance!D$ 3$317=quot;Vacquot;,ROW(Attendance!A$2:A$8)-ROW(Attendance!A$2) 1),ROWS($1:1)))quot;quot;)
gt;
gt; When I go to implement this it returns an error and highlights the
gt; double quotation marks at the very end.
gt;
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

quot;Beegequot; gt; wrote in message
...
gt; smoore,
gt;
gt; looks like it wants a comma before quot; quot;.
gt;
gt; Beege
gt;
gt; quot;smoorequot; gt; wrote in message
gt; oups.com...
gt;gt; Biff, Thanks for trying to help me. I've been scratching various
gt;gt; parts of my anatomy for some time over this. I'm glad someone has
gt;gt; finally come up with a solution.
gt;gt; Now need a little more help with the tweaking to get it to work in my
gt;gt; real worksheet. I've entered the following function in a sheet called
gt;gt; quot;Summaryquot;, all my data is in a sheet called quot;Attendancequot; . You will see
gt;gt; I've attempted to rewrite your function for this , but I'm still not
gt;gt; getting it right. Can you steer a rookie a little further? Thanks.
gt;gt;
gt;gt; IF(ROWS($1:1)lt;=COUNTIF(Attendance!D$3$317,quot;Vacquot;) ,Index(Attendance!B$3:B$317,SMALL(IF(Attendance!D$ 3$317=quot;Vacquot;,ROW(Attendance!A$2:A$8)-ROW(Attendance!A$2) 1),ROWS($1:1)))quot;quot;)
gt;gt;
gt;gt; When I go to implement this it returns an error and highlights the
gt;gt; double quotation marks at the very end.
gt;gt;
gt;
gt;
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.Sure, I'll catch ya after Survivor!

Biff

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