: : 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) )"
: 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.