# Formula help (solved again :))

#### The Idea Dude

##### Well-known Member
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

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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.

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.

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)),"")

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

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)),"")

Yep, works like a charm Andrew!!!

You folks CRANK!!!!!

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

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.

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

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.

Replies
4
Views
223
Replies
6
Views
376
Replies
3
Views
521
Replies
16
Views
1K
Replies
6
Views
1K

1,217,452
Messages
6,136,724
Members
450,025
Latest member
Beginner52

### 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.

### Which adblocker are you using?

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

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