Excel functions

[Klartigue]

In my "data" sheet, column G has a long list of different maturities dates in the format MM/DD/YY.

There are also three other sheets named "Early", "Mid", and "Late" that all have one row of data points and the columns coincide to 1 year (2015) 2 year (2016), 3 year (2017), etc..

If in the "data sheet", Say the maturity is 01/01/2015.
If the maturity has a month of January - March (the first thee rows on the example doc I attached), I would like it reference the "Early sheet" and then use to the year, 2015, to reference the data in 1yr column.

If in the "data sheet", if the maturity is from April to September, then reference the "Mid" sheet and depending on what the year of the maturity is, pull the data from the 1 yr, 2yr, etc.. column.

And then if the maturity on the data sheet is from October - December, reference the "Late" sheet and do the same.

So I was wondering if there is a general IF statement to say, if the month is January, than go to the early sheet, and it the year is current year +1, then reference column B. Etc...

See the attached for references..

[Bob Phillips]

Try

=INDIRECT(INDEX({"Early","Mid","Late"},INT((MONTH(G2)+3)/4))&"!"&CHAR(65+YEAR(G2)-YEAR(TODAY()+1))&"2")

[GTO]

@XLD:

Howdy Bob,

My partial attempt, which gave way when my eyelids crashed shut last night, was going to have INDIRECT() and a plethora of IFs.:blush

Anyways, given the middle part:


...If the maturity has a month of January - March ...it reference the "Early sheet" ... if the maturity is from April to September, then reference the "Mid" sheet ...And then if the maturity on the data sheet is from October - December, reference the "Late" sheet ...

Maybe?

=INDIRECT(INDEX({"Early","Mid","Late"},CHOOSE(INT((MONTH(G2)+2)/3),1,2,2,3))&"!"&CHAR(65+YEAR(G2)-YEAR(TODAY()+1))&"2")

Mark

[Klartigue]

That work's great, thank you!

[Klartigue]

That code works perfectly for me, but can you break it down into words so I understand fully what the code is doing?

[Bob Phillips]

Which one are you asking about?

[Bob Phillips]

Maybe?

=INDIRECT(INDEX({"Early","Mid","Late"},CHOOSE(INT((MONTH(G2)+2)/3),1,2,2,3))&"!"&CHAR(65+YEAR(G2)-YEAR(TODAY()+1))&"2")


I see, I was assuming 3 equal splits of 4 months each.

[GTO]

@XLD:

Just if you have the moment and yea, this will sound dopey...

Was CHOOSE() ideal, or would you do it differently?

Mark

[Klartigue]

I was talking about this one:


=INDIRECT(INDEX({"Early","Mid","Late"},CHOOSE(INT((MONTH(G2)+2)/3),1,2,2,3))&"!"&CHAR(65+YEAR(G2)-YEAR(TODAY()+1))&"2")

[Bob Phillips]

Was CHOOSE() ideal, or would you do it differently?

I would probably kick-off trying to do it some fancier, hopefully shorter way, then give up and admit that is just personal vanity, and that your way works, is clear, is concise, and perfectly fits the brief.

Are you going to explain it to Klartigue?

[GTO]

I would probably kick-off trying to do it some fancier, hopefully shorter way, then give up and admit that is just personal vanity, ...

Are you going to explain it to Klartigue?

:D Too funny; thanks!

Well, yours would be better, but here is mine:

@Klartique:

I do not know about you, but I find many help topics reference various worksheet functions to be a bit hard to follow, or I should say I feel less than confident as to truly understanding the topic after reading it. I won't repeat the help topics, as you can read these at will; but I will mention that I find it helpful to break-down formula examples to see what each piece is doing. This, along with the particular help topic, seems to get me a bit clearer on whatever it is I am not grasping at the moment. Of course once in a while, it still doesn't make sense, and if there is a particular function in the formula not making sense, don't hesitate to ask, and we can clear it up.

Okay, the formula... From your workbook, let us tear apart the formula as it would be in cell S7:


' =INDIRECT(INDEX({"Early","Mid","Late"},CHOOSE(INT((MONTH(G7)+2)/3),1,2,2,3))&"!"&CHAR(65+YEAR(G7)-YEAR(TODAY()+1))&"2")

To start building the String for INDIRECT, we can look at the parts.

=(MONTH(G7)+2)/3

Since what is in the parenthesis will evaluate first, we get =11/3, which returns 3.66666667. This is wrapped by INT(), so now we have 3.

So, as we use this 3 as the index for CHOOSE(), we return the third value (the second '2' listed).


This value (2) is then used in INDEX() to return the second element in Bob's array, "Mid"; which is then concatenated, so the first part of our string is "Mid!".

The next part gets us a column letter. Starting with =65+YEAR(G7)-YEAR(TODAY()+1) we see that 65 is used for CHAR() to index to the letter "A". As you are looking for future dates, the remainder subtracts and adds to see how many years future. Thus we return 69, which is used in CHAR(), to return "E". So now our string is "Mid!E", which is concatenated to "Mid!E2". This is then of course used in INDIRECT() to return the value 1 from cell E2 on the "Mid" worksheet.

Hope that helps,

Mark