Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Multiple IFs statements in Excel

jumpgem01jumpgem01 Posts: 1Member
I have a list of over 80 staff members in multiple worksheets, in the first worksheet I have placed a letter beside each to denote the department they belong to. I want to create a code that will check the name and apply the letter department code on the other worksheets. Can anyone help me with such a code. Would appreciate it, I am not really a programmer but still need help desperately.

Thanks Again,
Loy

Comments

  • MadifierMadifier Posts: 10Member
    : I have a list of over 80 staff members in multiple worksheets, in
    : the first worksheet I have placed a letter beside each to denote the
    : department they belong to. I want to create a code that will check
    : the name and apply the letter department code on the other
    : worksheets. Can anyone help me with such a code. Would appreciate
    : it, I am not really a programmer but still need help desperately.
    :
    : Thanks Again,
    : Loy
    :


    If I understood correctly, this is what I have for you.
    Using OpenOffice spreadsheet. Basically the same as Excel.

    place this in the cel that you want the department number to display
    "=INDIRECT( ADDRESS(MATCH(C2; A1:A4;0);2) )"

    Name___Department____input____output
    Sam____A____________Matt_____B
    Matt____B
    Dustin___C

    indirect( ref ) is used to display department value
    address( row, col ) is used to verify what cel you want
    match( input, list of names, 0 ) is used to search for the name against a list. the number zero is necessary to tell computer if list is sorted or not.

    thus you have:
    "=indirect( address( match(input, name, 0), column(department)))"

    One important item to note is the match(). if your list does not start at row 1, add the offset from 1 to the match()...
    address( match()+(row_offset), name, 0 )
  • zibadianzibadian Posts: 6,349Member
    : : I have a list of over 80 staff members in multiple worksheets, in
    : : the first worksheet I have placed a letter beside each to denote the
    : : department they belong to. I want to create a code that will check
    : : the name and apply the letter department code on the other
    : : worksheets. Can anyone help me with such a code. Would appreciate
    : : it, I am not really a programmer but still need help desperately.
    : :
    : : Thanks Again,
    : : Loy
    : :
    :
    :
    : If I understood correctly, this is what I have for you.
    : Using OpenOffice spreadsheet. Basically the same as Excel.
    :
    : place this in the cel that you want the department number to display
    : "=INDIRECT( ADDRESS(MATCH(C2; A1:A4;0);2) )"
    :
    : Name___Department____input____output
    : Sam____A____________Matt_____B
    : Matt____B
    : Dustin___C
    :
    : indirect( ref ) is used to display department value
    : address( row, col ) is used to verify what cel you want
    : match( input, list of names, 0 ) is used to search for the name
    : against a list. the number zero is necessary to tell computer if
    : list is sorted or not.
    :
    : thus you have:
    : "=indirect( address( match(input, name, 0), column(department)))"
    :
    : One important item to note is the match(). if your list does not
    : start at row 1, add the offset from 1 to the match()...
    : address( match()+(row_offset), name, 0 )

    Another option is to use the VLOOKUP() function.
  • ExcelGuruExcelGuru Posts: 2Member
    Hey Loy,

    Where are you at thus far? You should be able to qurery each separate worksheet and build a master. From there the manipulations will be much easier.

    Good luck,

    Conner
    [link=http://www.oneclickcommissions.com/excel-statements.html]Excel Statements[/link]
Sign In or Register to comment.