You're very welcome.....I'm glad we could resolve your issue..


***********
Regards,
Ron

XL2002, WinXP


"carl43m" wrote:

Thanks again for your excellent and timely help.
Carl

"Ron Coderre" wrote:

Carl

A few points:
1)The formula I posted is picking the last 2 characters from the week
reference and using that value to offset Col_A on Sheet1 and look for "m"s.

2) Excel stores each date as the number of days the date is from 12/31/1899
01/01/1900 is number 1
12/08/2006 is 39,059

3)Since you want to use dates....the formula must also change.
Using the same posted example, but with actual dates replacing week
references, this is the new ARRAY FORMULA for Sheet2,
B2:
=INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,MATCH($A2,Sheet1!$B$1:$N$1,0))="M",ROW( Sheet1!$B$2:$B$21)),B$1))

That formula uses the MATCH function find the position of the column heading
date on Sheet1 that matches the row heading date on Sheet2.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"carl43m" wrote:

Ron,
Thanks for your help. The spreadsheet si working as expected. I tried to
make one modification. Instead of using wk01, wk02,etc as the labels for my
13 week schedule, I tried to put in actual dates like jan 2, jan 9,etc. When
I do that then I get error messages. I tried using both a general format and
a custom format with mmm-dd and it still doesn't work. Is there something
unique to the formula that keeps it from working with dates? I can work with
spreadsheet the way it is but I was just trying to upgrade my understanding
of how to use the formulas. Thanks.
Carl

"Ron Coderre" wrote:

Sorry for the delay, Carl

Here are some of your Sheet 1 values:
wk01 wk02 wk03

And here are some of your Sheet2 values:
w1 w2 w3

See a slight difference?.....
Thought so! Sheet2 should have the same values as Sheet1

Wk01, instead of w1
or even W01
...the key is that the right 2 characters of the Sheet2 week references must
be digits.


Does that help?
***********
Regards,
Ron

XL2002, WinXP


"carl43m" wrote:

Ron,
I copied and pasted your formula directly into my spreadsheet model and then
did the ctr/shift/enter to make it an array formula and it still didn't work.
I then started with a new workbook from scratch and typed in the formula and
I still get a value error.

My wife tried it on her own with the second formula and that didn't work
eitheer.

Here is the snapshot of my model:


wk01 wk02 wk03
carl
tom m m
rayp m
raym m
al m
ralph
harvey m m
carol m
Karl m
bob m
jerry m
steve m
larry m
skip
wade m
jim m
susan
janet
marcie m
jackie m m


1 2 3
w1 #VALUE!
w2
w3
w4
w5
w6
w7
w8
w9
w10
w11
w12
w13


=INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,--RIGHT($A2,2))="m",ROW(Sheet1!$B$2:$B$21)),B$1))
When I do a copy and paste it doesn't show the { } for an array formula
but they are there in the spreadsheet.


Is there a way for me to attach my model to a post here that you could see
what might be happening?

Carl

"Ron Coderre" wrote:

Did you try building the sample model first to make sure that the basic
concept and formulas are functional?

***********
Regards,
Ron

XL2002, WinXP


"carl43m" wrote:

Ron,
Thanks for your suggestion, I trid both of the formulas but both came back
with n/a as an error message. I tried to rview the information on these
functions in excel help but I was unable to find whatever I may be doing
wrong to keep the formula from working. I tried to email you a copy of my
spreadsheet but it came back as undeliverable.
Carl

"Ron Coderre" wrote:

See if this example gets you headed in the right direction...

With
Sheet1, cells A1:N21 containing the data list
Where
A2:A21 contains Employee Names
B1:N1 contains Wk01, Wk02,....Wk13
B2:N21 contains the grid of assigned employees

On Sheet2....
A2:A14 contains Wk01, Wk02,....Wk13
B1:G1 contains 1,2,3,4,5,6

Put this ARRAY FORMULA* in
B2:
=INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,--RIGHT($A2,2))="M",ROW(Sheet1!$B$2:$B$21)),B$1))

Or...alternatively....this ARRAY FORMULA*
B2:
=INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,MATCH($A2,Sheet1!$B$1:$N$1,0))="M",ROW( Sheet1!$B$2:$B$21)),B$1))

(Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].)

Copy B2 and paste into C2:G2
Then...Copy B2:G2 and paste into A3:G14

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"carl43m" wrote:

I have a list of 20 names in Column A of my spread sheet. Then I have 13
columns which represent 13 weeks (1/4 of a year). Each week there are 6
people assigned to be moderators and I place the letter M in 6 different rows
within the column for the specific week. I also put other letters within
the columns to alert me as to who are participants vs moderators in any
individual week. If I want to generate a list of the 6 moderators each week
is there a function I can use that will identify the 6 row numbers that have
the letter M in them? Then I can use those row numbers to print myself a
list of the names that correspond with those row numbers.

My final goal is to be able to generate a list that will have 13 weeks down
the left side and to the right of each week number will be the 6 names of the
moderators for that week. Essentially a schudle for a quarter of the year.

Thanks
Carl