Formula help (solved again :))

[The Idea Dude]

Hi all,

I think this will be pretty easy to do, but I can't figure it out!!

I need to creat a formula for cell B5 that checks to see if cell A1="yes". If it does, then it will return the value of cell A2. If A1 does not equal "yes", then the formula will run the ceck on cell B2, and so on. I then need to be able to drag this equation downwards from cell B5.

So in the picture example, I will end up with a list like the YELLOW highlighetd cells, as opposed to the othe highlighted cells (which is what would happen from a simple "if" statement.

Thanks

Book2
	A	B	C	D	E	F	G	H
1		yes	yes	no	no	no	no	yes
2		hello	dog	cat	box	flower	rose	wet
3
4
5		hello		hello
6		dog		dog
7		wet
8
9
10
11				wet
12
Sheet1

This message was edited by The Idea Dude on 2002-09-09 01:57
This message was edited by The Idea Dude on 2002-09-09 02:35
This message was edited by The Idea Dude on 2002-09-09 18:54

 

[Gates Is Antichrist]

This would be fairly trivial in VBA (macros and/or functions you write), but if you really don't want to go there, I have a creative approach. Hopefully a more direct answer will come from another, but I'm not aware of it.

My cell choices correspond to your supplied graph. Good move, utilitizing it for asking your question.

E5 = if(D5 = "",0,1)
(you may want or need to go ISBLANK)
(actually ISTEXT is IDEAL here - check it out)
E6 through E999 are copies of E5

A4 = 0 (zero)
A5 = A4 + E5
A6 through A999 are copies of A5

F4 = 0
F5 = F4 + 1
F6 through F999 are copies of F5

Then B5 = vlookup(F5, $c$5:$D$999, 2, TRUE)
B6 through B999 are copies of B5

I used 999 for illustration. For best clarity, use a named range (and if you're unfamiliar w/ them, get familiar).

Messy - you can move things around, or hide stuff (less preferred), or select print area to clean up - but I believe this gets ths job done.

 

[Andrew Poulsom]

Try:

=IF(COUNTIF($A$1:$G$1,"yes")>ROW(A4)-ROW($A$4),INDEX(OFFSET($A$1,0,ROW(A1)-ROW($A$1)):$G$2,2,MATCH("yes",OFFSET($A$1,0,ROW(A1)-ROW($A$1)):$G$1,0)),"")

 

[The Idea Dude]

Thanks for the replies.

I tried the typed formula from andrew and it didn't quite work. I got the picture below.

I will try to nut out the one from GIA!

Book2
	A	B	C	D	E	F	G	H
1		yes	yes	no	no	no	no	yes
2		hello	dog	cat	box	flower	rose	wet
3
4
5		hello
6		hello
7
8
9
10
11
12
13
Sheet1

This message was edited by The Idea Dude on 2002-09-09 01:50

 

[Andrew Poulsom]

Sorry, I missed the blank column A:

=IF(COUNTIF($B$1:$H$1,"yes")>ROW(B4)-ROW($B$4),INDEX(OFFSET($B$1,0,ROW(B1)-ROW($B$1)):$H$2,2,MATCH("yes",OFFSET($B$1,0,ROW(B1)-ROW($B$1)):$H$1,0)),"")

 

[The Idea Dude]

Yep, works like a charm Andrew!!!

You folks CRANK!!!!!

 

[The Idea Dude]

Hi Andrew, the formula has actually not worked pwerfectly. Have a look at the picture, the yellow is the error.

Also, to throw a spanner in the works, I used dummy cells for ease, however, I had trouble modifying the formula. The real cells involved are

B7:IV7 for yes/no cells
B8:IV8 for the items below the yes/no cells
B53 where the equation goes

Sorry for this. I thought I could make the mods, but I didn't have muh luck!
Thanks again for your help!

Book2
	A	B	C	D	E	F	G	H
1		yes	yes	no	yes	yes	no	yes
2		hello	dog	cat	box	flower	rose	wet
3
4
5		hello
6		dog
7		box
8		box
9		flower
10
Sheet1

 

[Andrew Poulsom]

With existing functions I could not find a way to increment the column offset if "no" was found.

So I wrote this custom function:

Function MakeList(Rng As Range, Txt As Variant, ItemNo As Variant) As Variant
    Dim c As Range
    Dim x As Long
    Dim y As Long
    Dim TheItems() As Variant
'   Only contiguous range allowed
    If Rng.Areas.Count > 1 Then
        MakeList = CVErr(xlErrNA)
        Exit Function
    End If
'   Range cannot have more than 1 row
    If Rng.Count > Rng.Columns.Count Then
        MakeList = CVErr(xlErrNA)
        Exit Function
    End If
    x = 0
    y = 0
    For Each c In Rng
        y = y + 1
        If c.Value = Txt Then
            x = x + 1
        End If
        If x = ItemNo Then Exit For
    Next c
    If ItemNo > x Then
        MakeList = ""
        Exit Function
    End If
    Set Rng = Rng.Offset(1, 0)
    MakeList = Application.Index(Rng, 1, y)
End Function

The arguments are:

1. Rng - the range containing your yes/no text.
2. Txt - the text to be found (in your case "yes").
3. ItemNo - the occurrence of Txt to be found.

Paste the function into a genaral module in your workbook.

Then enter the following formula in cell B53:

=MakeList($B$7:$IV$7,"yes",ROW(B53)-ROW(B$52))

and copy down.

 

[Scott R]

Enter this in B5 and copy down as necessary:
=INDEX(B$1:H$2,2,SMALL(IF((B$1:H$1="yes"),(COLUMN(B$1:H$1)-COLUMN(B$1)+1)),ROWS(B$13:B13)))

Forgot to add that this should be an array formula (Ctrl+Shift+Enter).
This message was edited by Scott R on 2002-09-09 11:28

 

[Andrew Poulsom]

That's excellent Scott R.

To get rid of the #NUM errors if copied too far:

=IF(COUNTIF($B$1:$H$1,"yes")<=ROW(B1)-ROW($B$1),"",INDEX(B$1:H$2,2,SMALL(IF((B$1:H$1="yes"),(COLUMN(B$1:H$1)-COLUMN(B$1)+1)),ROWS(B$1:B1))))

I was trying to avoid array formulas.