Capture.PNG
I managed to create a code that looks for values and does some simple calculations before values are put in 2nd and 3rd row.
How can I make the code do this for rest of the rows in Sheet1?
What I'm trying to achieve is:
Cell J2 =VLOOKUP($I3;Sheet2!$I:$AA;MATCH(Sheet1!J1;Sheet2!$I$1:$AA$1;0);0) -VLOOKUP($I2;Sheet2!$I:$AA;MATCH(Sheet1!J1;Sheet2!$I$1:$AA$1;0);0)
I3 value fetched from Sheet2 minus I2 value fetched from Sheet2
Cell J3 = (VLOOKUP($I3;Sheet2!$I:$AA;MATCH(Sheet1!J1;Sheet2!$I$1:$AA$1;0);0)-1) / VLOOKUP($I2;Sheet2!$I:$AA;MATCH(Sheet1!J1;Sheet2!$I$1:$AA$1;0);0)
I3 value fetched from Sheet2 minus 1 divided by I2 value fetched from Sheet2
Cell J4 = VLOOKUP($I5;Sheet2!$I:$AA;MATCH(Sheet1!J1;Sheet2!$I$1:$AA$1;0);0) -VLOOKUP($I4;Sheet2!$I:$AA;MATCH(Sheet1!J1;Sheet2!$I$1:$AA$1;0);0)
Cell J5 = (VLOOKUP($I5;Sheet2!$I:$AA;MATCH(Sheet1!J1;Sheet2!$I$1:$AA$1;0);0)-1) / VLOOKUP($I4;Sheet2!$I:$AA;MATCH(Sheet1!J1;Sheet2!$I$1:$AA$1;0);0)
The formula continues down to last row and to the right to last column. Lookup_value and column index are dynamic.
Bookmarks