Excel help?? Ok lets try and explain what I want to do I have a worksheet with some publication data in. Column A has the email address in so JohnSmith@hotmail.com may show up 10 times because he has 10 publications. In another worksheet (in the same file) I have a key of email addresses and Staff ID numbers. So JohnSmith@hotmail.com has a staff ID of 2468. What I need to do is replace the email address in the publications worksheet with the Staff ID numbers in the key worksheet.
it'll probably be easier to email the file to one of the nerds, let them sort it then mail i back. i think. *runs and hides*
It's bugging me cause I know you can do it really easily with some kind =() type code, but I can't figure it out
Ok.. here we go.. You have 2 worksheets - say 1 called "a" and 1 called "b" In "a" you should have 3 columns of data.. "ID" & "Email" & "Publications" In "b" you should have 2 colums of data "ID" & "Email" Sheet A In the ID column create a drop down box with a input range of the table of IDs in sheet b. (do this by right clicking and formatting control) It should read something similar to this - 'b'!$A$2:$A$11 This makes the box lookup cells a2 - a11 on sheet b. Next create a cell link - 'b'!$A$13 This make the number that is selected in the drop down box appear in cell A13 on sheet b. Now its gonna get complicated.. This is the only way I could work round this probelm I'm afraid. On Sheet b select the customer IDs and go insert -> name -> define. Then type in ID and click add. On sheet A at the end of the data (ie the column after the data has ended) input - =MATCH('b'!A13,ID,0) This matches the chosen number with the number in the name ID.. Now in the box where you want the email to appear input - =INDEX('b'!A2:A11, MATCH(a!*insert the cell number at the end of the data*,ID,0)) I think that should work.. I might be a little confused though.. I could send you my working assignment with it in if ya want.. whats your email? :upyours:
