Very long If Statements, Alternatives???

[chris186h]

Hello everyone. I have a formula which i copy down over roughly 500 cells. The formula is quite long and i was wondering if there was any alternative that would make it smaller and easier to use and also faster.

=IF($A$3="Earlies",IF(TODAY()>=DATE(2015,6,27),INDEX($B$7:$GE$500,MATCH(A6,'EARLIES WEEKS 27-52'!$B$7:$B$500,0),MATCH($A$2,'EARLIES WEEKS 27-52'!$B$7:$GE$7,0)),IF(TODAY()<=DATE(2015,6,26),INDEX($B$7:$GE$500,MATCH(A6,'EARLIES WEEKS 1-26'!$B$7:$B$500,0),MATCH($A$2,'EARLIES WEEKS 1-26'!$B$7:$GE$7,0)))),IF($A$3="Lates",IF(TODAY()>=DATE(2015,6,27),INDEX($B$7:$GE$500,MATCH(A6,'EARLIES WEEKS 27-52'!$B$7:$B$500,0),MATCH($A$2,'EARLIES WEEKS 27-52'!$B$7:$GE$7,0)),IF(TODAY()<=DATE(2015,6,26),INDEX($B$7:$GE$500,MATCH(A6,'EARLIES WEEKS 1-26'!$B$7:$B$500,0),MATCH($A$2,'EARLIES WEEKS 1-26'!$B$7:$GE$7,0)),
IF($A$3="Nights",IF(TODAY()>=DATE(2015,6,27),INDEX($B$7:$GE$500,MATCH(A6,'EARLIES WEEKS 27-52'!$B$7:$B$500,0),MATCH($A$2,'EARLIES WEEKS 27-52'!$B$7:$GE$7,0)),IF(TODAY()<=DATE(2015,6,26),INDEX($B$7:$GE$500,MATCH(A6,'EARLIES WEEKS 1-26'!$B$7:$B$500,0),MATCH($A$2,'EARLIES WEEKS 1-26'!$B$7:$GE$7,0)))))))))

Basically i have the information spread across three workbooks. Each one called Earlies, Lates or Nights. (the above shows it as on different sheets, this was for testing purposes until i add the links to the other closed workbooks)

Cell A3 will show either Earlies, Lates, Nights depending on the time. cell A6 will contain a name which is where i copy the formula down over hundreds of cells. A2 Contains the lookup date.
B7:GE7 contains dates
$B$7:$B$500 contains names
$B$7:$GE$500 contains names with the matching data

Thankyou for any help you can provide

 

[ukmikeb]

Hi

Your basic formula is :-

=IF(TODAY()>=DATE(2015,6,27),INDEX($B$7:$GE$500,MATCH(A6,'EARLIES WEEKS 27-52'!$B$7:$B$500,0),MATCH($A$2,'EARLIES WEEKS 27-52'!$B$7:$GE$7,0)),
INDEX($B$7:$GE$500,MATCH(A6,'EARLIES WEEKS 1-26'!$B$7:$B$500,0),MATCH($A$2,'EARLIES WEEKS 1-26'!$B$7:$GE$7,0)))

Not tested - check the brackets!
It seems to be the same whether it is Earlies, Lates or Nights.

NB If you have already tested the date as being equal to or greater than 27/6/2015 then if it doesn't satisfy that test it must be equal to or less than 26/6/2015.

If you need to change the sheet references within the Match sections then wrap the above in the If Earlies/Lates and then default (no test) for Nights if they are the only possible values for cell A3.

hth

 

[chris186h]

Thankyou for your reply. Im sorry but i have noticed from your reply that i did enter the wrong code

=IF($A$3="Earlies",IF(TODAY()>=DATE(2015,6,27),INDEX($B$7:$GE$500,MATCH(A6,'EARLIES WEEKS 27-52'!$B$7:$B$500,0),MATCH($A$2,'EARLIES WEEKS 27-52'!$B$7:$GE$7,0)),IF(TODAY()<=DATE(2015,6,26),INDEX($B$7:$GE$500,MATCH(A6,'EARLIES WEEKS 1-26'!$B$7:$B$500,0),MATCH($A$2,'EARLIES WEEKS 1-26'!$B$7:$GE$7,0)))),IF($A$3="Lates",IF(TODAY()>=DATE(2015,6,27),INDEX($B$7:$GE$500,MATCH(A6,'Lates WEEKS 27-52'!$B$7:$B$500,0),MATCH($A$2,'Lates WEEKS 27-52'!$B$7:$GE$7,0)),IF(TODAY()<=DATE(2015,6,26),INDEX($B$7:$GE$500,MATCH(A6,'Lates WEEKS 1-26'!$B$7:$B$500,0),MATCH($A$2,'Lates WEEKS 1-26'!$B$7:$GE$7,0)),
IF($A$3="Nights",IF(TODAY()>=DATE(2015,6,27),INDEX($B$7:$GE$500,MATCH(A6,'Nights WEEKS 27-52'!$B$7:$B$500,0),MATCH($A$2,'Nights WEEKS 27-52'!$B$7:$GE$7,0)),IF(TODAY()<=DATE(2015,6,26),INDEX($B$7:$GE$500,MATCH(A6,'Nights WEEKS 1-26'!$B$7:$B$500,0),MATCH($A$2,'Nights WEEKS 1-26'!$B$7:$GE$7,0)))))))))

Each section (earlies lates nights) should check their corresponding sheet. I have made the changes in the code above.
Once again thankyou

 

[ukmikeb]

Replace the MATCH formulas in post #2 to :-

1. - MATCH(A6,INDIRECT(ADDRESS(7,2,1,,$A$3&" Weeks 27-52")&":"&ADDRESS(500,2,1)),0) 

2. - MATCH($A$2,INDIRECT(ADDRESS(7,2,1,,$A$3&" Weeks 27-52")&":"&ADDRESS(7,187,1)),0)

3. - MATCH(A6,INDIRECT(ADDRESS(7,2,1,,$A$3&" Weeks 1-26")&":"&ADDRESS(500,2,1)),0) 

4. - MATCH($A$2,INDIRECT(ADDRESS(7,2,1,,$A$3&" Weeks 1-26")&":"&ADDRESS(7,187,1)),0)

Then either Earlies will have to be put in cell A3 as "EARLIES" or alternatively change the sheet name to mixed case.

hth