Shorten formula that adds two cells if condition is met

[Alphonse68]

Hello everyone,

I have this situation:

=IF(Bdg!$B$9=1,Sch!AJ8+Sch!AN8, IF(Bdg!$B$9=2,Sch!AJ16+Sch!AN16, IF(Bdg!$B$9=3,Sch!AJ24+Sch!AN24, ..., IF(Bdg!$B$9=12,Sch!AJ96+Sch!AN96)

where Bdg(B9) has the number of the current month and Sch(AJ+AN) have values to add up in a pattern of 8 rows apart for each month.
The formula works fine, but it is very long and there are variations of it in maaaany places... editing them becomes really tedious.

I want to know if there is a way of writing this formula so that it is a) more practical to edit and/or b) shorter?

I apologize if there is a post somewhere that answers my questions, but, honestly, I have no idea how to even begin searching for it.

Any and all hints are welcome.

Cheers!!

 

[Nine Zero]

try this out

=IF(AND(Bdg!$B$9>=1,Bdg!$B$9<=12),INDIRECT("Sch!AJ"&(8*Bdg!$B$9))+INDIRECT("Sch!AN"&(8*Bdg!$B$9)),"B9 does not have a valid month!")

This will take care of all of your calculations

EDIT: didnt see the part about the number in B9 being a month... modified now to only accept numbers 1-12

 

[Special-K99]

Try

IF(AND(Bdg!$B$9>=1,Bdg!$B$9<=12),INDEX(Sch!AJ8:AJ96,(Bdg!$B$9-1)*8+1,1)+INDEX(Sch!AN8:AN96,(Bdg!$B$9-1)*8+1,1))

but if you know Bdg!$B$9 will be an integer in the range 1 to 12 then just

=INDEX(Sch!AJ8:AJ96,(Bdg!$B$9-1)*8+1,1)+INDEX(Sch!AN8:AN96,(Bdg!$B$9-1)*8+1,1)

should suffice

 

[Alphonse68]

Nine Zero and Special-K99,
Thank you both for great answers... I wouldn't have come close to imagining either answer.
I will go the the latter of K99's, but Nine's answer help me out for another dilemma I had and was about to post.
Thank you both for I have learned something new today, didn't just get answer.
CHEERS!!

 

[Nine Zero]

No problem, glad i could help