Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filling a cell based on another cell result (2003 SP2)

    Good afternoon

    I have a cell linked to a list whereby I can choose a variety of letters as abbreviations for absences H for Holiday, S for Sickness etc. What I would now like to do is enter a formula that says if any of these letters have been selected 2 'time formatted cells HH:MM' are filled in, something along the lines of =IF(L6:L40="H","S","T","M","P","U",E6:E40="08:00", F6:F40="18:00") but as usual I can't get my (thick) head around it, any pointers appreciated

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

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

    Re: Filling a cell based on another cell result (2003 SP2)

    In E6 enter the formula

    IF(ISERROR(SEARCH(L6,"HSTMPU")),"",TIME(8,0,0))

    In F6 enter the formula

    IF(ISERROR(SEARCH(L6,"HSTMPU")),"",TIME(18,0,0))

    Select E6 and F6, then fill down to row 40.

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling a cell based on another cell result (2003 SP2)

    Hi Hans

    Thanks for the quick response, however I cannot have anything in the cells because other factors in the workbook depend on what is in it, that is way I tried to show my attempted formula in the 3rd person as it were so that I could shove out of the way somwhere in a hidden cell.

    Sorry for the confusion

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

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

    Re: Filling a cell based on another cell result (2003 SP2)

    A formula cannot directly place a value another cell than the cell it is in. The only alternative is VBA, but you've indicated several times that you'd rather not use that...

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling a cell based on another cell result (2003 SP2)

    A formula can only return a value to the cell that it is in, it can not change another cell. What you want to do can be done using VBA code in the worksheet change event routine. If you want help doing this, please upload a sample workbook that can be used for testing.
    Legare Coleman

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling a cell based on another cell result (2003 SP2)

    Thanks both Hans and Legare

    I am (honestly) creeping into the 21st century and overlast weekend managed to apply a background colour in a workbook with VBA, by the 22nd century I may get to a 'User Form' but I will keep plodding on.

    Thanks Hans for your original Formula and this reply, in order to trick Excel I thought that I would set up a pseudo column out of sight that I could use seperatley in my work time additions, however as soon as I put the formula into the cells it shows 08:00 and 18:00 and does not seem conditional on L6 showing one of the required letters?

    Thanks

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

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

    Re: Filling a cell based on another cell result (2003 SP2)

    The formula does work, so I'd have to see the workbook...

  8. #8
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling a cell based on another cell result (2003 SP2)

    Hi Hans

    I would never be so presumptious to think it could be anybody other than me if something did not work, sorry if that is what it seemed to imply. I have attached an example of what I am trying to do but it seems that although L6 onwards has no selection E6 and F6 etc. are still populated by the formula.

    Thanks

    Steve
    Attached Files Attached Files
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

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

    Re: Filling a cell based on another cell result (2003 SP2)

    No, it was my fault! Sorry about that. I had only tested with correct and incorrect values, but not with blanks. Try this in E6:
    <code>
    =IF(OR(ISBLANK(L6),ISERROR(SEARCH(L6,"HSTMPU")))," ",TIME(8,0,0))
    </code>
    and similar in F6.

  10. #10
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling a cell based on another cell result (2003 SP2)

    Thanks Hans

    thats just the ticket, I can understand most of it but why ISERROR?

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Filling a cell based on another cell result (2003 SP2)

    If the letter is not found, SEARCH gives an error. If it is found, it gives the position.

    Steve

Posting Permissions

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