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?