I sent you my model spreadsheet with your formula on sheet2 --maybe
something will jump out you -- I have no idea what I am doing incorrectly.

Carl

"Ron Coderre" wrote:

I'm a bit puzzled.....I followed what you last posted and the model works fine.

See if this helps:

A clip from Sheet1, cells A1:C7
(blank) Wk01 Wk02
Name01 m (blank)
Name02 (blank) m
Name03 (blank) (blank)
Name04 m (blank)
Name05 (blank) m
Name06 (blank) (blank)

A clip from Sheet2, cells A1:D3
(blank) 1 2 3
Wk01 Name01 Name04 Name07
Wk02 Name02 Name05 Name08

The ARRAY FORMULA in Sheet2, B2 is:
=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))

Commited with [ctrl][shift][enter]

Did you do something different?
***********
Regards,
Ron

XL2002, WinXP


"carl43m" wrote:

Ron,
Yes, I built a spreadsheet with 20 names in column a from a2 to a21. Then I
put 13 weeks across the top columns B1 thru N1. I then populated 6 people
for each week with the letter m in the 13 weeks of columns. I then went to
sheet two and set it up with the names of 13 weeks in column A rows 2-14 and
in B1-G1 the numbers 1 thru 6.

I then went to b2 and entered the first formula as an array formula and got
n/a, so I then erased that formula and tried formula 2 and got n/a again.

"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