Thanks Peo it worked great.
Sorry it took so long getting back, but I have been under the weather. I was
able to understand how Debra did it. I didn't know to select my range prior.
(She had selected 8 rows, then applied the array.) Now, I have another
question. It has to do with the data validation page (monday in her example).
Debra if your still checking in on this thread, feel free to weigh in.
I wanted to use a different page for my dealer road map. The person using
this schedule requested I make it as much like her sheet's orignal "look" as
I can, so she can just jump right in and use it without having to get used to
a new sheet.
I tried to apply data validation to her sheet named "Mon" (now in this
workbook) to no avail. First I tried to use Debra's formula that she used in
the example for me. This:
=OFFSET(INDIRECT("Mon"&B2),1,0,COUNT(INDIRECT("Mon "&B2&"Col")),1)
It didn't work. So I tried to change it to see if it would work. Nope.
Nothing I did worked. I tried to set up my own list, and it wouldn't let me
because the list is on another page. So I used Column AC (on her example
sheet) and named it as a range. This worked but it let me put the same dealer
on 2 different games at the same time. Not good. I can't see why Debra's
worked and I can't make it work. So, did I give you enough info? What do I do
to make it work on my sheet? Again, thanks for the help.



"Peo Sjoblom" wrote:

Robb,

"Robb27" wrote in message
...
Hi Debra,

After reviewing your new example, the light bulb came on about seeing how
it
works. I did come up with one question though. I have 56 dealers total and
this is what I did.
I took a shortcut:
I copied my dealer list to your Col A (A2-A57).
I thought I could just drag the fill handle down and copy each cell down
to
Row 57 in Col L thru R as well as T thru Z, AC thru AI and AK thru AQ.
So, I extend all columns down to row 57.

I can't see why, but in Cols L-R and AC-AI (after I leave your original 7
rows), the formula changes from:
{IF(ROW($A2:$A8)ROW(A2)+1COUNT(T2:T8),"",INDEX($A :$A,SMALL(T2:T8,ROW(INDIRECT("1:"&ROWS(A2:A8)))))) }

to this:

IF(ROW($A9:$A15)ROW(A9)+1COUNT(T9:T15),"",INDEX($ A:$A,SMALL(T9:T15,ROW(INDIRECT("1:"&ROWS(A9:A15))) )))
- (this changes every 8 rows)

It will increase in groups of 8 since it is an array formula that has been
entered in one fell swoop over
an array which is 8X7 cells so if you want to use Debra's workbook you need
to select L2:R57 or something like that then array enter

=IF(ROW($A2:$A57)-ROW(A2)+1COUNT(T2:T57),"",INDEX($A:$A,SMALL(T2:T5 7,ROW(INDIRECT("1:"&ROWS(A2:A57))))))

The formulas in T2:Z2 can be copied down to row 57 in the regular way and so
do those in AK2:AQ2 but the formulas in AC2:AI2 needs to be dealt with the
same way as those in L2:R57

Btw, this is how you array enter Debra's formula, copy the formula I posted,
then click in the name box (above column A) and type L2:R57, and press
enter, click in the formula bar and highlight the formula that's in there,
replace the formula with the one I posted using Ctrl + V, then finish it
off with Ctrl + shift & enter


-

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey