Alter formula based on todays date

[nickmax1]

=IFERROR(INDIRECT("'" & VLOOKUP($K$35,{"Q1","Jan";"Q2","Apr";"Q3","Jul";"Q4","Oct"},2,0) & " " & $L$35 & "'!B9"),0)+IFERROR(INDIRECT("'" & VLOOKUP($K$35,{"Q1","Feb";"Q2","May";"Q3","Aug";"Q4","Nov"},2,0) & " " & $L$35 & "'!B9"),0)+IFERROR(INDIRECT("'" & VLOOKUP($K$35,{"Q1","Mar";"Q2","Jun";"Q3","Sep";"Q4","Dec"},2,0) & " " & $L$35 & "'!B9"),0)

The above formula works brilliantly in summing up the cell B9 in several worksheets based on their name and where they fall in the calendar quarter. My worksheets are labelled Mmm YYYY (ie Aug 2013, Jan 2013 etc)

I want to be able to change the above formula so that if the month in the selected quarter is in the future then B10 is used instead of B9.

For example. Today we are in May.
If I wanted to see the sum of Q2 2013 I would want to see the sum of Apr 2013 cell B9, May 2013 cell B9 and Jun 2013 B10, as June is in the future.

Any help greatly appreciated.

[nickmax1]

edited to show whole formula

[nickmax1]

can anyone far cleverer than me take a look at this?

[nickmax1]

what can i do to assist in making this solveable?

[Fotis1991]

Perhaps a sample workbook?

[nickmax1]

sample uploaded with more explanation

[kvsrinivasamurthy]

Pl see attached file.

[nickmax1]

thanks kvsrinivasamurthy
It seems to work for the cell you tested - I have copied the formula to the other three and changed the ranges but it doesnt work....what am I missing ?

see attached

[kvsrinivasamurthy]

The formula in Q43 Uses E10 in Jun 2013 sheet for jun month,But E10 value is blank.
The formula in Q48 Uses H10 in Jun 2013 sheet for jun month,But H10 value is blank.
Therefore you are getting same result .Put values in E10 and H10 You will find difference.

[nickmax1]

kvsrinivasamurthy
Sorry I have made a mistake in my explanation, if the month is in the future I want that months cell B8 (or E8 or H8 depending on Company, Lloyds or EU Corporate) instead of B10, E10, H10., so simply changing those has fixed it nicely!

I have tested on future quarters and it works brilliantly.
Rep added - marking as solved.

[nickmax1]

I would love to know how you got it to work, as i dont follow this code?

"'!"&IF((MATCH($K$35,{"Q1","Q2","Q3","Q4"},0)*3)-2>MONTH(TODAY()),"B8","B9")),0)

[nickmax1]

So sorry but after further testing i need a slight amendment. I need the formula for months in the PAST, to use B6 (E6 and H6 accordingly)

So April for this quarter would use B6, May would use B9 and June would use B8....

[kvsrinivasamurthy]

IN the formula for present month B9 and future months B10 is used.Change B9 or B10 according to your requirement.

IF((MATCH($K$35,{"Q1","Q2","Q3","Q4"},0)*3)-2>MONTH(TODAY()),"B8","B9")),0)

In the above formula According to selected Quarter It calculates the Month Number and compares with Present Month Number and select B8 or B9 Depending on True or False.

Eg;Q2 selected First Month of Q2 is April , Month number calculated is (2*3)-2 Which is 4 , Which compared with Present Month Number 5.

I hope I am clear.

With Regards
KVS

[nickmax1]

thanks kvsrinivasamurthy
jhave you had a look at my further amendment needed to select B6 (E6 or H6) if the month is in the past?

thanks!

[nickmax1]

managed to figure it out......phew that took me ages

=IFERROR(INDIRECT("'" & VLOOKUP($K$35,{"Q1","Jan";"Q2","Apr";"Q3","Jul";"Q4","Oct"},2,0) & " " & $L$35 & "'!"&IF((MATCH($K$35,{"Q1","Q2","Q3","Q4"},0)*3)-2>MONTH(TODAY()),"B8",IF((MATCH($K$35,{"Q1","Q2","Q3","Q4"},0)*3)-2<MONTH(TODAY()),"B6","B9"))),0)+IFERROR(INDIRECT("'" & VLOOKUP($K$35,{"Q1","Feb";"Q2","May";"Q3","Aug";"Q4","Nov"},2,0) & " " & $L$35 & "'!"&IF((MATCH($K$35,{"Q1","Q2","Q3","Q4"},0)*3)-1>MONTH(TODAY()),"B8",IF((MATCH($K$35,{"Q1","Q2","Q3","Q4"},0)*3)-1<MONTH(TODAY()),"B6","B9"))),0)+IFERROR(INDIRECT("'" & VLOOKUP($K$35,{"Q1","Mar";"Q2","Jun";"Q3","Sep";"Q4","Dec"},2,0) & " " & $L$35 & "'!"&IF((MATCH($K$35,{"Q1","Q2","Q3","Q4"},0)*3)>MONTH(TODAY()),"B8",IF((MATCH($K$35,{"Q1","Q2","Q3","Q4"},0)*3)<MONTH(TODAY()),"B6","B9"))),0)