Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    '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 !
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    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.
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    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....
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #7
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    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
  •