Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Assignment of a Cell Address In Formula
I would like to modify the following formula such that the cell
address J$8354 is replaced by a dynamic cell address formula. The original formula (in array format) is: {=CELL("address",INDEX(J$8:J$8354,MATCH(MAX((J$8:J $8354<=K$6)*J$8:J $8354),J$8:J$8354,0)))} The dynamic formula that would substitute cell address J$8354 is: =ADDRESS(MATCH(99^99,J:J),10) Unfortunately, when I merge the two formulas together, I get an error... Is it even possible to do what I have in mind without resorting to VBA? Thanks. -- tb |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Assignment of a Cell Address In Formula
Hi,
Is your ultimate objective to get the Row Address which contains the value appearing in cell K6 ? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Assignment of a Cell Address In Formula
On Dec 13, 9:12 am, Carim wrote:
Hi, Is your ultimate objective to get the Row Address which contains the value appearing in cell K6 ? Hi. Yes, the ultimate objective would be to find the cell address which contains the value <= to the one in cell K$6. (There could be more than one cell with the same value and <= to the one in K6. I would need to find the address of the cell with the biggest row number. So, if K$6's value is "80.0%", and both cells J1234 and J5678 share the value 80.0%, the formula would return address $J$5678 because that is the cell with the biggest row number and with the biggest value <= to K6.) Once I can successfully develop this formula, I would incorporate it in yet another formula... Should I go ahead and post this formula too, or do you have enough info? Thanks. -- tb |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Assignment of a Cell Address In Formula
OK then ... could following formula help :
=ADDRESS(SUMPRODUCT(MAX((ROW(J8:J9000))*(J8:J9000< =K6))),2,4) HTH |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Assignment of a Cell Address In Formula
On Dec 13, 10:48 am, Carim wrote:
OK then ... could following formula help : =ADDRESS(SUMPRODUCT(MAX((ROW(J8:J9000))*(J8:J9000< =K6))),2,4) HTH Hi, Carim. Unfortunately, the formula does not seem to work. I always get J9000 as the cell address. I am also looking for a way to DYNAMICALLY incorporate in the formula the last cell address (in your formula, statically indicated as "J9000") as that address will change in time and I do not want to run the risk of forgetting to manually adjust the range in the formula. The last cell address would be the cell in column J that has the closest value <= to cell K6. Should there be more than one cell in column J with the same value which also happens to be the closest value to K6, then I need the address of the cell that has the biggest row number containing such value. I hope I'm making sense... Thanks. -- tb |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Assignment of a Cell Address In Formula
Hi again,
Just tested formula which is working at my end ... To answer specifically your question, you can use offset() like in this example : =SUM(J8:OFFSET(J8,COUNT(J8:J965536),0)) HTH |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Dynamic Cell Address As Formula Location | Excel Worksheet Functions | |||
Dynamic Formula with Dynamic Address | Excel Worksheet Functions | |||
Average Function and dynamic cell address | Excel Worksheet Functions | |||
Dynamic flexible cell address | Excel Discussion (Misc queries) | |||
ADDRESS function - dynamic input cell | Excel Discussion (Misc queries) |