# Thread: 'Searching' or 'Lookup' formula (Excel 2003)

1. ## 'Searching' or 'Lookup' formula (Excel 2003)

Hi all.....I need help with an existing formula that I have in a spreadsheet.....I have alreayd received some help thru this site (previously), but I am attaching a simplified version of my sample WBook.

Page 1 is a "Job Scheduling" sheet; quarterly, I enter employees' dates when they are not available to work. As the quarter unfolds, I enter job sites and assign employees. This data flows to page 2 (called "Employees") where they can see their assignments, their N/A dates and whether any N/A date conflicts with a 'work' date....to give them better, clearer information, I want to adjust (expand) the formula on the "Employees" sheet so that it will also automatically enter a "W" under their name if they are working on a certain day.....I put better explanations on the pages within the spreadsheet....thanks for any suggestions or help.....and, Happy St Patrick's Day !

2. ## Re: 'Searching' or 'Lookup' formula (Excel 2003)

You could put the formula

=IF(ISERROR(SEARCH(E\$4,\$C5,FIND("/",\$C5)+1)),IF(AND('Job schedule'!E6="x",COUNTIF(\$C5,"*"&E\$4&"*")=1),"c:x" ,'Job schedule'!E6),"W")

in E5, and fill down then right. But you'll lose the c:x entries.

3. ## Re: 'Searching' or 'Lookup' formula (Excel 2003)

..Thanks you Hans.....I see that....and, when I carry the formula across, I also lose the "x" (because it displays "FALSE").....and I really need to keep the c:x.....is there perhaps a way to set up the Employee sheet where all cells are W (by default); I then use cond. formatting to conceal the W---white/white etc) and the W on the employee sheet is revealed & changes to an X if I input an X on the corresponding cell on the Job Schedule sheet, and, the W is also revealed (as a W) if their name is assigned?....would something like that enable me to also preserve the c:x aspect ?.....I will reattach the testsample in case you discarded the original...thanks.

4. ## Re: 'Searching' or 'Lookup' formula (Excel 2003)

Sorry, I don't understand what you want. Could you attach an example of what you want the end result to be, with an explanation?

5. ## Re: 'Searching' or 'Lookup' formula (Excel 2003)

I am attaching testsample 2.....I have outlined what I did in an atempt to preserve the C:X designation, as well as show the X (not available) and have the W displayed as & where it should....

6. ## Re: 'Searching' or 'Lookup' formula (Excel 2003)

Try this in E5:

=IF(ISERROR(SEARCH(E\$4,\$C5,1+SEARCH("/",\$C5))),'Job schedule'!E6,IF(UPPER('Job schedule'!E6)="X","C:X","W"))

Fill down then right, or use Copy, then Paste Special > Formulas to extend the formula to E5:J12. No conditional formatting needed unless you want it.

7. ## Re: 'Searching' or 'Lookup' formula (Excel 2003)

Thank you again, Hans.....that worked out better than what I had, especially b/c it reduced the file size....have a good weekend

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•