Replacing "offset" with "index"

[eliasthebest]

Hey guys!

Can anyone help me replace the "offset" with "index" in the following formula?

=IF(
AND(OFFSET(V12,0,-$I38,1,1)-OFFSET(W12,0,-$I38,1,1)=OFFSET(V12,0,-$I38,1,1),OFFSET(V12,0,-$I38,1,1)<>0),OFFSET(V30,0,-$I38,1,1)+OFFSET(V31,0,-$I38,1,1)-OFFSET(W31,0,-$I38,1,1)+OFFSET(W32,0,-$I38,1,1)-OFFSET(V32,0,-$I38,1,1),
IF(OFFSET(V2,0,-$I38,1,1)=1,OFFSET(V12,0,-$I38,1,1)+OFFSET(V32,0,-$I38,1,1)-OFFSET(G32,0,-$I38,1,1)+OFFSET(G31,0,-$I38,1,1)-OFFSET(V31,0,-$I38,1,1),
IF(OFFSET(V12,0,-$I38,1,1)=0,0,OFFSET(V12,0,-$I38,1,1)+OFFSET(U31,0,-$I38,1,1)-OFFSET(V31,0,-$I38,1,1)+OFFSET(V32,0,-$I38,1,1)-OFFSET(U32,0,-$I38,1,1)+V39)))

Thanks!!

 

[Jonmo1]

Welcome to the board.

I won't do all of them, but the method is the same for all (see how I do 1, you can do the rest)..

OFFSET(V12,0,-$I38,1,1)
Can be changed to
INDEX(12:12,22-$I38)

12:12 is the row reference from V12
22 is the column number of V

 

[pgc01]

Hi

Also:

=INDEX(12:12,COLUMN(V:V)-I38)

in the case you want to drag it across

 

[offthelip]

If i was doing this i would write some vba to do it. It might take as long as editing by hand but definitely more fun

 

[eliasthebest]

Jommo1 - thank you very much!! it worked!

 

[Jonmo1]

You're welcome.