|
simple formula?
Hello, Again i find myself in need of what should be an easy formula...but
unable to find what i need. I appreciate any help. I have a small list of email addresses (AB18:AB22). I have a pull down menu linked to cell AB17 with the peoples name in it. I simply want one cell to display the email address of the person chosen in the pull down. This seems too simple to even ask.... Thanks |
simple formula?
=INDEX(AB18:AB22,MAX(IF(NOT(ISERROR(LEFT(AB18:AB22 ,FIND("@",AB18:AB220)-1)=A
B17)),ROW(AB18:AB22)-MIN(ROW(AB18:AB22))+1,0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "jrose" wrote in message ... Hello, Again i find myself in need of what should be an easy formula...but unable to find what i need. I appreciate any help. I have a small list of email addresses (AB18:AB22). I have a pull down menu linked to cell AB17 with the peoples name in it. I simply want one cell to display the email address of the person chosen in the pull down. This seems too simple to even ask.... Thanks |
simple formula?
This sounds like a job for VLOOKUP(). -- BruceP ------------------------------------------------------------------------ BruceP's Profile: http://www.excelforum.com/member.php...o&userid=33653 View this thread: http://www.excelforum.com/showthread...hreadid=534959 |
simple formula?
I appreciate the help cuz this is way over my head... however it does not
work. It returns just one of the email addresses on the list. "Bob Phillips" wrote: =INDEX(AB18:AB22,MAX(IF(NOT(ISERROR(LEFT(AB18:AB22 ,FIND("@",AB18:AB220)-1)=A B17)),ROW(AB18:AB22)-MIN(ROW(AB18:AB22))+1,0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "jrose" wrote in message ... Hello, Again i find myself in need of what should be an easy formula...but unable to find what i need. I appreciate any help. I have a small list of email addresses (AB18:AB22). I have a pull down menu linked to cell AB17 with the peoples name in it. I simply want one cell to display the email address of the person chosen in the pull down. This seems too simple to even ask.... Thanks |
simple formula?
I thought that was what you wanted.
List your data and expected results, it always helps. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "jrose" wrote in message ... I appreciate the help cuz this is way over my head... however it does not work. It returns just one of the email addresses on the list. "Bob Phillips" wrote: =INDEX(AB18:AB22,MAX(IF(NOT(ISERROR(LEFT(AB18:AB22 ,FIND("@",AB18:AB220)-1)=A B17)),ROW(AB18:AB22)-MIN(ROW(AB18:AB22))+1,0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "jrose" wrote in message ... Hello, Again i find myself in need of what should be an easy formula...but unable to find what i need. I appreciate any help. I have a small list of email addresses (AB18:AB22). I have a pull down menu linked to cell AB17 with the peoples name in it. I simply want one cell to display the email address of the person chosen in the pull down. This seems too simple to even ask.... Thanks |
simple formula?
ok. I did not make it clear. sorry. I have 5 email address..each in a cell
AB24:28. I have a pulldown with those peoples names that is linked to cell AB17, returning a number 1-5 in that cell. I just want the email address associated with the person that is selected in the pull down to be displayed in a given cell. Does that make sense? "Bob Phillips" wrote: I thought that was what you wanted. List your data and expected results, it always helps. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "jrose" wrote in message ... I appreciate the help cuz this is way over my head... however it does not work. It returns just one of the email addresses on the list. "Bob Phillips" wrote: =INDEX(AB18:AB22,MAX(IF(NOT(ISERROR(LEFT(AB18:AB22 ,FIND("@",AB18:AB220)-1)=A B17)),ROW(AB18:AB22)-MIN(ROW(AB18:AB22))+1,0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "jrose" wrote in message ... Hello, Again i find myself in need of what should be an easy formula...but unable to find what i need. I appreciate any help. I have a small list of email addresses (AB18:AB22). I have a pull down menu linked to cell AB17 with the peoples name in it. I simply want one cell to display the email address of the person chosen in the pull down. This seems too simple to even ask.... Thanks |
simple formula?
Ah, you get the INDEX in the linked cell, I was thinking Data Validation
returning the value. So just use =INDEX(AB18:AB22,AB17) I have kept your original ranges not the latest ones <g -- HTH Bob Phillips (remove nothere from email address if mailing direct) "jrose" wrote in message ... ok. I did not make it clear. sorry. I have 5 email address..each in a cell AB24:28. I have a pulldown with those peoples names that is linked to cell AB17, returning a number 1-5 in that cell. I just want the email address associated with the person that is selected in the pull down to be displayed in a given cell. Does that make sense? "Bob Phillips" wrote: I thought that was what you wanted. List your data and expected results, it always helps. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "jrose" wrote in message ... I appreciate the help cuz this is way over my head... however it does not work. It returns just one of the email addresses on the list. "Bob Phillips" wrote: =INDEX(AB18:AB22,MAX(IF(NOT(ISERROR(LEFT(AB18:AB22 ,FIND("@",AB18:AB220)-1)=A B17)),ROW(AB18:AB22)-MIN(ROW(AB18:AB22))+1,0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "jrose" wrote in message ... Hello, Again i find myself in need of what should be an easy formula...but unable to find what i need. I appreciate any help. I have a small list of email addresses (AB18:AB22). I have a pull down menu linked to cell AB17 with the peoples name in it. I simply want one cell to display the email address of the person chosen in the pull down. This seems too simple to even ask.... Thanks |
simple formula?
Thanks so much. That was a much easier formula. How would i make the cell
where this formula is a clickable link. The email address is now returned into this given cell but it is not a clickable link to the address. Is that possible? "Bob Phillips" wrote: Ah, you get the INDEX in the linked cell, I was thinking Data Validation returning the value. So just use =INDEX(AB18:AB22,AB17) I have kept your original ranges not the latest ones <g -- HTH Bob Phillips (remove nothere from email address if mailing direct) "jrose" wrote in message ... ok. I did not make it clear. sorry. I have 5 email address..each in a cell AB24:28. I have a pulldown with those peoples names that is linked to cell AB17, returning a number 1-5 in that cell. I just want the email address associated with the person that is selected in the pull down to be displayed in a given cell. Does that make sense? "Bob Phillips" wrote: I thought that was what you wanted. List your data and expected results, it always helps. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "jrose" wrote in message ... I appreciate the help cuz this is way over my head... however it does not work. It returns just one of the email addresses on the list. "Bob Phillips" wrote: =INDEX(AB18:AB22,MAX(IF(NOT(ISERROR(LEFT(AB18:AB22 ,FIND("@",AB18:AB220)-1)=A B17)),ROW(AB18:AB22)-MIN(ROW(AB18:AB22))+1,0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "jrose" wrote in message ... Hello, Again i find myself in need of what should be an easy formula...but unable to find what i need. I appreciate any help. I have a small list of email addresses (AB18:AB22). I have a pull down menu linked to cell AB17 with the peoples name in it. I simply want one cell to display the email address of the person chosen in the pull down. This seems too simple to even ask.... Thanks |
| All times are GMT +1. The time now is 12:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com