IF/then statement help.....

[HxR]

I am trying to write a If statement but I do not know how to make it answer the way I want it to.
I have two worksheets.
Sheet1 is where the answer will be. Sheet2 is where I am getting my information.

Sheet2:
Column:
A-----------B
Show1 Sunday
Show2 Monday
Show3 Sunday
Show4 Monday
Show5 Friday
Show6 Tuesday

I want to say if col B = Monday then show what is next to the monday ie. Show2 and Show4. I want to be able to run this on more than one cell showing all results in seperate cells.

This is something I do not think can be done but I am wondering if maybe someone might have an idea.
Thank you

[Richard Buttrey]

On Sat, 4 Mar 2006 10:46:20 -0600, HxR
<HxR.245mha_1141491001.3426@excelforum-nospam.com> wrote:

>
>I am trying to write a If statement but I do not know how to make it
>answer the way I want it to.
>I have two worksheets.
>Sheet1 is where the answer will be. Sheet2 is where I am getting my
>information.
>
>Sheet2:
>Column:
>A-----------B
>Show1 Sunday
>Show2 Monday
>Show3 Sunday
>Show4 Monday
>Show5 Friday
>Show6 Tuesday
>
>I want to say if col B = Monday then show what is next to the monday
>ie. Show2 and Show4. I want to be able to run this on more than one
>cell showing all results in seperate cells.
>
>This is something I do not think can be done but I am wondering if
>maybe someone might have an idea.
>Thank you

Two options.

First, and probably the easiest is a Pivot Table with the days and
"shows" as the row and column headings, and a Count of the shows in
the data area. You then get a matrix which in the example above will
show the number 1 at the intersection of Monday and Show 2, and a 1 at
the intersection of Monday and Show4

The second is Data-->Filter-->Advanced Filter-->Copy to another
location..

Put Field headings above your data in sheet 2, say "Show" and "Day",
Create a criteria range A1:A2 on Sheet 1 with "Day" in A1 and whatever
day you want to know about in A2.

Put "Show" and "Day" in A4:B4 on sheet 2 and make this the outpout
range and then do your data filter.

That's the basic technique. if you want to see seven output ranges,
one for each day on sheet 1, then you'll need to run this seven times
each time selecting a different output range and criteria.

You could smarten the criteria selection up by creating a list of days
somewhere, and put a Data-->Validation on A2, and choose the 'list'
option from the "allow' list box, and then point to your list of days.

HTH
Richard Buttrey
__

[Bob Phillips]

Assuming that the test value is in A1, a group of cells to contain the
results (max possible), and in the formula bar, enter

=IF(ISERROR(SMALL(IF(Sheet2!$B$1:$B$20=A1,ROW($A1:$A20),""),ROW($A1:$A20))),
"",
INDEX(Sheet2!$A$1:$A$20,SMALL(IF(Sheet2!$B$1:$B$20=A1,ROW($A1:$A20),""),ROW(
$A1:$A20))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"HxR" <HxR.245mha_1141491001.3426@excelforum-nospam.com> wrote in message
news:HxR.245mha_1141491001.3426@excelforum-nospam.com...
>
> I am trying to write a If statement but I do not know how to make it
> answer the way I want it to.
> I have two worksheets.
> Sheet1 is where the answer will be. Sheet2 is where I am getting my
> information.
>
> Sheet2:
> Column:
> A-----------B
> Show1 Sunday
> Show2 Monday
> Show3 Sunday
> Show4 Monday
> Show5 Friday
> Show6 Tuesday
>
> I want to say if col B = Monday then show what is next to the monday
> ie. Show2 and Show4. I want to be able to run this on more than one
> cell showing all results in seperate cells.
>
> This is something I do not think can be done but I am wondering if
> maybe someone might have an idea.
> Thank you
>
>
> --
> HxR
> ------------------------------------------------------------------------
> HxR's Profile:
http://www.excelforum.com/member.php...o&userid=32147
> View this thread: http://www.excelforum.com/showthread...hreadid=519001
>

[Martin P]

With your sample information in Sheet 2, cells A1 to B6, enter the following:
Cell C1: =SUMPRODUCT(--(B1=$B$1:B1))
Cell D1: =ROW()
Copy C1:D1 to C1:D6
Enter Sunday to Saturday in F2 to F6
G1: =COLUMN(F1)-COLUMN($F$1)+1
Copy G1 to G1:J1
G2: =SUMPRODUCT($D$1:$D$6,--($B$1:$B$6=$F2),--($C$1:$C$6=G$1))
Copy G2 to G2:J8
Now in Sheet 1:
In A2:A8 enter Sunday to Saturday
B2: =IF(Sheet2!G2<>0,INDEX(Sheet2!$A$1:$A$6,Sheet2!G2),"")
Copy B2 to B2:E8


"HxR" wrote:

>
> I am trying to write a If statement but I do not know how to make it
> answer the way I want it to.
> I have two worksheets.
> Sheet1 is where the answer will be. Sheet2 is where I am getting my
> information.
>
> Sheet2:
> Column:
> A-----------B
> Show1 Sunday
> Show2 Monday
> Show3 Sunday
> Show4 Monday
> Show5 Friday
> Show6 Tuesday
>
> I want to say if col B = Monday then show what is next to the monday
> ie. Show2 and Show4. I want to be able to run this on more than one
> cell showing all results in seperate cells.
>
> This is something I do not think can be done but I am wondering if
> maybe someone might have an idea.
> Thank you
>
>
> --
> HxR
> ------------------------------------------------------------------------
> HxR's Profile: http://www.excelforum.com/member.php...o&userid=32147
> View this thread: http://www.excelforum.com/showthread...hreadid=519001
>
>