I have a table of data running about 686 rows. On the right most column I have some row numbers arrived at using Aggregate function. I want to enter a formula in the column next to it (i.e. in the new column). The formu;a will use the previous column’s row number and create a range address for matching the values of the first column within the range starting from the row following the row number arrived at based on Aggregate fuction in the previous column to the row previous to the row where the formula is being entered. I then want to copy the formula to all the rows. The formula should therefore have the relative address of the row previous to the row where ever the formula is copied. I have tried using the indirect function, but it expects me to put a string at the end of the previous row address also making it a string and the to address in the range in all copied rows therefore remains unchanged. (For example in row 16, if I have in W16 the value of the Aggregate function, in column X, I have to writre the function as Indirect("A"&W16+1&":$A15")
I tried reversing the address ( with higher value of the row coming first followed by the lower value of the row at the end (i.e., instead of indirect("A"&W16+1 &":$A15")
, I tried indirect($A15&":A:"&w16+1)
. But this is not working. I also tried Indirect("A"&W16&":"&$A15)
. I was not working. With Address function put inside an indirect function, I am not sure whether I will be able to create a range address which will change address as per the row in which it is copied. Is there any other way or is there anyother function which I am missing.
esbee51