If, then display

P

puiuluipui

Hi, i have in A1:L1 all the months. (january, february....)
Below every month i have cell with some numbers. I need in C8 to display
numbers below curent month. If this month is september, then the code to look
in A1:L1 and find september and to display numbers below. If this month in
october to find october and display below numbers.
Can this be done?
Thanks!
 
P

puiuluipui

Hi, it's working if in A1:L1 i have dates. (01.09.2009.....)
But i have text (January, February....)
Can this formula work with month name?
Thanks!
 
M

Mike H

Hi,

This depends on what you actually have in A1 - L1 and I have assumed
properly formatted dates. Try this array formula

=INDEX(A2:L2,MATCH(MONTH(TODAY()),MONTH(A1:L1)))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike
 
M

Mike H

Hi,

You should do it properly, dates are dates and not text. format these dates
as mmmm and they will display as January etc.

Mike
 
P

puiuluipui

Hi Mike, It's working now. I have one more question. If in cell below
september is nothing the code display "0".
Can the code display a message? "No data for this month".
Can this be done?
Thanks!
 
R

Rick Rothstein

Assuming your January, February, etc. entries in A1:L1 are **text** (that
is, the spelled out names and *not* dates formatted to look like the month
names), you can use this (normally-entered) formula...

=SUMIF(A1:L1,TEXT(TODAY(),"mmm"),A2:L2)
 
P

puiuluipui

Hi Rick, it's working, but i need the formula to display "No entry" or some
text message if in cell 2 is nothing.
Can this be done?
Thanks!
 
R

Rick Rothstein

You didn't say anything about "No Entry" in your original request. Try this
normally entered formula then...

=IF(SUMIF(A1:L1,TEXT(TODAY(),"mmm"),A2:L2)=0,"No
Entry",SUMIF(A1:L1,TEXT(TODAY(),"mmm"),A2:L2))

You can change the "No Entry" text to whatever wording you want (just make
sure that text is enclosed in quote marks as shown for the "No Entry" text).
 
P

puiuluipui

It's working!
Thanks!

Rick Rothstein said:
You didn't say anything about "No Entry" in your original request. Try this
normally entered formula then...

=IF(SUMIF(A1:L1,TEXT(TODAY(),"mmm"),A2:L2)=0,"No
Entry",SUMIF(A1:L1,TEXT(TODAY(),"mmm"),A2:L2))

You can change the "No Entry" text to whatever wording you want (just make
sure that text is enclosed in quote marks as shown for the "No Entry" text).
 
P

puiuluipui

Hi, i have one more question.
I need this code to be modified to display next cell;

Ex: -this is what code is doing now-
The code is in C8 and extract 125
A1=september
B1=125

This is what i need:
The code is in C8 and extract 425
A1=september
B1=125 B2=425

Can the code display the cell next to the one is displayed now? (cell+1 or
something, but without specify the cell)
Can this be done?
Thanks!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top