Problems with If logic for months. If this month, then.

[dceremuga]

Good Day.

I've been running in circles with this one.

What I wish to accomplish is simple, yet it's leaving me perplexed.

If January, then the number I have in another cell, and Feb through December should be 0. Then as the months tick off, Jan through the month chosen will be assigned a cell, and any months forward would be 0. For instance, if July is chosen as the current month, then c13=y4 and so on up till July, Aug through December would be 0 or "".

If you need a further explanation, I can try to explain further. I don't have any hair left on this simple problem!

[JohnTopley]

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[dceremuga]

Please see attached. I've highlighted where the month is chosen and to the right of that the numbers that should populate.

[AliGW]

For instance, if July is chosen as the current month, then Y1=x20, Y2=x21, Y3=x22, Y4=x23, Y5=X24, Y6=X25, Y7=X26, Aug through December would be 0 or "".

There is nothing in those cells in column X. Where should Excel find the data?

[dceremuga]

There is nothing in those cells in column X. Where should Excel find the data?

I was using that as an example. Now that you have the worksheet to reference, the true value of January would be c13=y4 and so on... It'll make sense now. I should almost delete this post and start over since I now understand what is needed to get help.

[AliGW]

No - you must not start a new thread. Here's something to get you started (you need calculation set to automatic):

=IF(ROWS(Y$4:Y4)<=MATCH($O$4,$X$4:$X$15,0),C13,"")

Copy this down, then edit C13 for each month. You will find that the months populate as you change the selector in O4.

[dceremuga]

No - you must not start a new thread. Here's something to get you started (you need calculation set to automatic):

=IF(ROWS(Y$4:Y4)<=MATCH($O$4,$X$4:$X$15,0),C13,"")

Copy this down, then edit C13 for each month. You will find that the months populate as you change the selector in O4.

This is not working. When I put this in C13 and the month chosen is say "July", it simply puts in a 0. If the month July is chosen, January, February, March, April, May, June and July should get their value, everything after should get "". Unless I'm doing something wrong here. I want this to auto populate the month chosen and all of the previous months. This sounds odd, but it has a purpose for what I'm doing.

[AliGW]

Attach the sheet where you have attempted to apply the formula. Yes, it does work - I have tested it.

Did you edit the C13 reference in the 11 other formulae to match the correct month? have you switched on auto-calculation?

[AliGW]

Explain the formula in C13 - what is it supposed to be doing?

[dceremuga]

Ok, I put your formula into C13 exactly as typed. =IF(ROWS(Y$4:Y4)<=MATCH($O$4,$X$4:$X$15,0),C13,""). Then in cell C21 I put the formula =IF(ROWS(Y$4:Y4)<=MATCH($O$4,$X$4:$X$15,0),C21,"")

C13 should contain the value of Y4.

[AliGW]

Oh, OK - you said the yellow area. Forget it, then - I had the wrong end of the stick - sorry.

[dceremuga]

Maybe this will help. I would put this formulate into C13, C21, c29, c37, H13, h21, h29, h37, m13, m21, m29, m37

If O4=X4 then C13=Y4, every thing else is ""
If O4=X5 then C21=Y5 and C13=Y4, otherwise ""
If O4=X6 then C29=Y6 and C21=y5 and C13=Y4, otherwise ""

and so on.

[AliGW]

Not really, unless you want long IF formulae. What would be better would to be change the cells where the months are referenced to custom date format, then you can use the month number to decide whether or not the cell should be populated.

[AliGW]

Have a look at the attached. I've changed the formatting of the cells referencing months and then used this formula:

=IF(MONTH($O$4)>=MONTH(A12),Y4,0)

[dceremuga]

You are a genius. It works! Exactly what I was looking for. I had started to do LONG if statements. I see how you changed the date format. Nicely done sir.

Answered.

[AliGW]

Not a sir - I hope I look female.

You are welcome. If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.