Formula help (solved again :))

The Idea Dude

Well-known Member
Joined
Aug 15, 2002
Messages
589
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
ABCDEFGH
1yesyesnonononoyes
2hellodogcatboxflowerrosewet
3
4
5hellohello
6dogdog
7wet
8
9
10
11wet
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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Well-known Member
Joined
Aug 15, 2002
Messages
589
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
ABCDEFGH
1yesyesnonononoyes
2hellodogcatboxflowerrosewet
3
4
5hello
6hello
7 
8 
9 
10 
11 
12 
13 
Sheet1

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

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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

Well-known Member
Joined
Aug 15, 2002
Messages
589

ADVERTISEMENT

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
ABCDEFGH
1yesyesnoyesyesnoyes
2hellodogcatboxflowerrosewet
3
4
5hello
6dog
7box
8box
9flower
10 
Sheet1
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
With existing functions I could not find a way to increment the column offset if "no" was found.

So I wrote this custom function:

Code:
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

Active Member
Joined
Feb 20, 2002
Messages
437
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.
 

Forum statistics

Threads
1,143,920
Messages
5,721,541
Members
422,369
Latest member
redinator

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top