Dragging formula along columns with changing reference cells

[excel_lisa]

Hi,

I wrote the following formulas in Cell B2, C2 and D2 of Sheet 2:
B2: =IF(AND(Sheet1!T2<20160000;Sheet1!U2>20150000);Sheet1!V2;"No")
C2: =IF(AND(Sheet1!X2<20160000;Sheet1!Y2>20150000);Sheet1!Z2;"No")
D2: =IF(AND(Sheet1!AB2<20160000;Sheet1!AC2>20150000);Sheet1!AD2;"No")

I want to drag this formula all the way along the next 96 columns.
It should continue as follows:
E2: =IF(AND(Sheet1!AF2<20160000;Sheet1!AG2>20150000);Sheet1!AH2;"No")
F2: =IF(AND(Sheet1!AJ2<20160000;Sheet1!AK2>20150000);Sheet1!AL2;"No")
etc.

However, in reality it continues as follows:
E2: =IF(AND(Sheet1!W2<20160000;Sheet1!X2>20150000);Sheet1!Y2;"No")
F2: =IF(AND(Sheet1!AA2<20160000;Sheet1!AB2>20150000);Sheet1!AC2;"No")
etc.

How can I alter the formula so that it can continue how I want it to?
Thank you in advance!

 

[StephenCrump]

Welcome to the Forum!

Try:

B2: =LET(StartCol;16+4*COLUMNS($B2:B2);IF(AND(INDEX(Sheet1!2:2;StartCol)<20160000;INDEX(Sheet1!2:2;StartCol+1)>20150000);INDEX(Sheet1!2:2;StartCol+2);"No"))

 

[excel_lisa]

Thanks a lot for your reply! Unfortunately the LET function is not recognised by my Excel. Is there a way to do this without the LET function?

 

[jasonb75]

Using the formula suggested by @StephenCrump without LET,

=IF(AND(INDEX(Sheet1!2:2;16+4*COLUMNS($B2:B2))<20160000;INDEX(Sheet1!2:2;17+4*COLUMNS($B2:B2))>20150000);INDEX(Sheet1!2:2;18+4*COLUMNS($B2:B2));"No"))

 

[excel_lisa]

Thanks a lot!