How to determine the cell location?
Does anyone have any suggestions on how to determine the cell location within
table? The range of table is from C10 to AU54, there is a list of numbers within this table Starting from C10 is 1, D10 is 56, ... AU10 is 1981 C11 is 2, D11 is 57, ... AU11 is 1982 C12 is 3, D12 is 58, ... AU12 is 1983 .... C54 is 45, D54 is 90, ... AU54 is 2025 There is a given value in BB1, which is 197, it should return the cell location G26 in cell BB2. Does anyone have any suggesitons? Thanks in advance for any suggestions Eric |
How to determine the cell location?
Your table doesn't match your description.
I'll bet D10 = 46 not 56. To find the address of the cell that contains 197: Arrary entered** : =ADDRESS(MAX((C10:AU54=BB1)*ROW(C10:AU54)),MAX((C1 0:AU54=BB1)*COLUMN(C10:AU54)),4) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Eric" wrote in message ... Does anyone have any suggestions on how to determine the cell location within table? The range of table is from C10 to AU54, there is a list of numbers within this table Starting from C10 is 1, D10 is 56, ... AU10 is 1981 C11 is 2, D11 is 57, ... AU11 is 1982 C12 is 3, D12 is 58, ... AU12 is 1983 ... C54 is 45, D54 is 90, ... AU54 is 2025 There is a given value in BB1, which is 197, it should return the cell location G26 in cell BB2. Does anyone have any suggesitons? Thanks in advance for any suggestions Eric |
How to determine the cell location?
Hi,
You could try this formula =ADDRESS(IF(C49-45*FLOOR(C49/45,1)=0,45,C49-45*FLOOR(C49/45,1)),CEILING(C49/45,1)+2) Please note that formula is based on the premise that the numbers are listed consecutively and the difference between lowest and highest is 44. Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Eric" wrote in message ... Does anyone have any suggestions on how to determine the cell location within table? The range of table is from C10 to AU54, there is a list of numbers within this table Starting from C10 is 1, D10 is 56, ... AU10 is 1981 C11 is 2, D11 is 57, ... AU11 is 1982 C12 is 3, D12 is 58, ... AU12 is 1983 ... C54 is 45, D54 is 90, ... AU54 is 2025 There is a given value in BB1, which is 197, it should return the cell location G26 in cell BB2. Does anyone have any suggesitons? Thanks in advance for any suggestions Eric |
All times are GMT +1. The time now is 01:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com