Validation List
Hi,
I have a dropdown from a validation list. I want to reference the cell that the list is pointing to. Ex. my list where the dropdown is located is E71, the list is located at AC69 and is 23 rows. When an item is selected from the list, I want to reference the cell in the list (AC69-AC91) not E71. -- TIA AFJr |
Validation List
I want to reference the cell in the list (AC69-AC91) not E71.
For what purpose? Does this mean you want the cell address? Do you want to use the address as a reference in a formula? -- Biff Microsoft Excel MVP "AFJr" wrote in message ... Hi, I have a dropdown from a validation list. I want to reference the cell that the list is pointing to. Ex. my list where the dropdown is located is E71, the list is located at AC69 and is 23 rows. When an item is selected from the list, I want to reference the cell in the list (AC69-AC91) not E71. -- TIA AFJr |
Validation List
Yes I want to use the cell address as a reference for a formula.
Thanks for taking the time to respond. -- TIA AFJr "T. Valko" wrote: I want to reference the cell in the list (AC69-AC91) not E71. For what purpose? Does this mean you want the cell address? Do you want to use the address as a reference in a formula? -- Biff Microsoft Excel MVP "AFJr" wrote in message ... Hi, I have a dropdown from a validation list. I want to reference the cell that the list is pointing to. Ex. my list where the dropdown is located is E71, the list is located at AC69 and is 23 rows. When an item is selected from the list, I want to reference the cell in the list (AC69-AC91) not E71. -- TIA AFJr |
Validation List
Depending on how robust this needs to be, try one of these:
Robust version Arrary entered** =ADDRESS(MAX((AC69:AC91=E71)*ROW(AC69:AC91)),COLUM N(AC69:AC91),4) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Not so robust version Normally entered ="AC"&MATCH(E71,AC69:AC91,0)+68 Both will return a *TEXT* string in the form of a relative cell address. However, as is, it can't be used as a reference in a formula. You would need to wrap these formulas inside an INDIRECT function like so: =INDIRECT(ADDRESS(MAX((AC69:AC91=E71)*ROW(AC69:AC9 1)),COLUMN(AC69:AC91),4)) =INDIRECT("AC"&MATCH(E71,AC69:AC91,0)+68) This has the net effect of using the value at the cell address as a reference but you'll get the same thing if you just reference the drop down cell!!!!! For example: E71 drop down = Joe Joe is located in the source list at address AC89 Without the use of INDIRECT both formulas will return the *TEXT* string AC89. To make this a usable cell address we include the INDIRECT function. Then both formulas will return Joe. So, in essence we took the long way around to return Joe when all we needed to do was reference the drop down cell E71. -- Biff Microsoft Excel MVP "AFJr" wrote in message ... Yes I want to use the cell address as a reference for a formula. Thanks for taking the time to respond. -- TIA AFJr "T. Valko" wrote: I want to reference the cell in the list (AC69-AC91) not E71. For what purpose? Does this mean you want the cell address? Do you want to use the address as a reference in a formula? -- Biff Microsoft Excel MVP "AFJr" wrote in message ... Hi, I have a dropdown from a validation list. I want to reference the cell that the list is pointing to. Ex. my list where the dropdown is located is E71, the list is located at AC69 and is 23 rows. When an item is selected from the list, I want to reference the cell in the list (AC69-AC91) not E71. -- TIA AFJr |
All times are GMT +1. The time now is 01:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com