I want a formula that looks up for a specific text in a table, and then add a controlled number of columns in that text's row.
=-SUM(OFFSET('IS Act'!$C$11, 0, 0, 1, CONTROL!B5))
Using the formula above, I want 'IS Act'!$C$11 to be determined by the identity of the text. In other words if the text is not in C11 but C77 then the formula above should read
=-SUM(OFFSET('IS Act'!$C$77, 0, 0, 1, CONTROL!B5))
Note CONTROL!B5 controls how many columns I want eveluated.
I had tried to use
=ADDRESS(MATCH($B$37,'IS Act'!$B:$B,0),3,,1,"IS Act")
In this case my text for the lookup is in B37, and once this is found in IS Act I want the formula to then sum the number of columns specified by Control!B5.
Thanks.
=-SUM(OFFSET('IS Act'!$C$11, 0, 0, 1, CONTROL!B5))
Using the formula above, I want 'IS Act'!$C$11 to be determined by the identity of the text. In other words if the text is not in C11 but C77 then the formula above should read
=-SUM(OFFSET('IS Act'!$C$77, 0, 0, 1, CONTROL!B5))
Note CONTROL!B5 controls how many columns I want eveluated.
I had tried to use
=ADDRESS(MATCH($B$37,'IS Act'!$B:$B,0),3,,1,"IS Act")
In this case my text for the lookup is in B37, and once this is found in IS Act I want the formula to then sum the number of columns specified by Control!B5.
Thanks.