0

Imagine a workbook with two sheets, named "Hosts" and "Eligibility".

The Hosts sheet is laid out like this:

Name    Status    Eligibility
-----------------------------
Josh    Ready     Yes
Anita   Off       No

The Eligibility sheet is laid out like this

Ineligibility Reason            Description
---------------------------------------------------------------
Off                             Person is off for the duration
Disqualified                    Person is not qualified for the run                             

I'm trying to construct a formula in the "Eligibility" column. It examines the cell one column to the left (Status), and if the word there matches anything in the first column of the eligibility sheet (A:A), it should say "No", otherwise it should say "Yes".

I've constructed such a formula, and placed it in cell C2 of the Hosts sheet.

The formula looks like:

=IF(ISERROR(SEARCH(B2,Eligibility!A:A)),"Yes", "No")

When I go to drag the fill handle down to copy the formula into the other cells in column C, the formula isn't copied. That is, the cells filled over are blanked entirely, and the formula is not present.

Worth mentioning that I can copy and paste the formula cell, and it pastes correctly (and updates the first cell reference.. if I pasted into cell C5, the first reference would correctly become B5). This, however, is a pain.

What's happening with the fill handle here? Is the problem with my formula, or with my Excel settings?

1

1 Answer 1

1

Not really an answer to your question but perhaps it might be easier using a VLOOKUP in place of the SEARCH function to simplify it.

Also I believe the search function will find a sub-string, so if you had an ineligibility reason of "jerkoff" for example it would pick that up also.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .