If empty, then display message

P

puiuluipui

Hi, i need this code to display a message if next or last (when it finds
first empty cell) cell in A1:L1 is empty.

=INDEX(A1:L1,MATCH(MONTH(TODAY()),MONTH(A2:L2)-1))
Can this be done?
Thanks!
 
P

puiuluipui

Hi Dan, in A1:L1 i have:
rate 1 rate 2 rate 3 rate 4.........rate 12
in A2:L2 i have
01.01.2009 01.02.2009 01.03.2009 ....01.12.2009

12 rates, 12 month. This is one of the rates. If i have only 7 rates
remaining, but the same range, the code will find an empty cell at some
point. So, i need to display a message.
EX:
this month will display "rate 7" (the last rate)
next month the code will find empty cell after "rate 7" and i neet to
display a message.

Can this be done?
Thanks!
 
P

puiuluipui

Hi Jacob, i added "+1" to display next rate, and i changed ranges.
A1:L1= rates
A3:L3= billings nr
Your code now: =INDEX(A1:L1,MATCH(9^9,A3:L3)+1)

All i need is this code to display a message if in A1:L1 finds an empty cell.
Mesaage to be "No more rates"
Can this be done?
Thanks!
 
B

Bob Phillips

=IF(ISERROR(INDEX(A1:L1,MATCH(9^9,A3:L3)+1)),"No more
rates",INDEX(A1:L1,MATCH(9^9,A3:L3)+1))
 
B

Bob Phillips

If using Excel 2007, you can use

=IFERROR(INDEX(A1:L1,MATCH(9^9,A3:L3)+1),"No more rates")
 
P

puiuluipui

Hi Bob, it's not displaying message. It's displaying "0"
I have 2 ranges: A1:L1 and A3:L3
A1:L1 have : rates 1, rates 2...rates 10. (first 10 cells are filled, and
the last 2 cells are empty)
A3L:3 have random numbers. (first 10 cells are filled and the last 2 are
empty)
I need in the code to display next rate. In this case there is no more
rates, so i need to display a message.
Ex:
A1=rate 1 ; A2=some number -- the code display next rate = rate 2
B1= rate 2 ; B2=some number -- the code display next rate = rate 3
....
J1=rate 10 ; J2=some number -- -- the code display next rate = rate 11 or
"no more rates" if I1 is empty

Cell I1 is empty. Cell "I1" does not contain "rate 11", so the code to
display instead of "rate 11", the message "no more rates".

I Work with ecxel 2003.
Thanks allot Bob!
 
P

puiuluipui

Hi Bob, it's not displaying message. It's displaying "0"
I have 2 ranges: A1:L1 and A3:L3
A1:L1 have : rates 1, rates 2...rates 10. (first 10 cells are filled, and
the last 2 cells are empty)
A3L:3 have random numbers. (first 10 cells are filled and the last 2 are
empty)
I need in the code to display next rate. In this case there is no more
rates, so i need to display a message.
Ex:
A1=rate 1 ; A2=some number -- the code display next rate = rate 2
B1= rate 2 ; B2=some number -- the code display next rate = rate 3
....
J1=rate 10 ; J2=some number -- -- the code display next rate = rate 11 or
"no more rates" if I1 is empty

Cell I1 is empty. Cell "I1" does not contain "rate 11", so the code to
display instead of "rate 11", the message "no more rates".

I Work with ecxel 2003.
Thanks allot Bob!
 
B

Bob Phillips

I think this is what you want

=IF(INDEX(A1:L1,MATCH(9^9,A2:L2)+1)="","No more
rates",INDEX(A1:L1,MATCH(9^9,A2:L2)+1))
 
P

puiuluipui

Yes, this is exactly what i want.
Thanks!

Bob Phillips said:
I think this is what you want

=IF(INDEX(A1:L1,MATCH(9^9,A2:L2)+1)="","No more
rates",INDEX(A1:L1,MATCH(9^9,A2:L2)+1))

--
__________________________________
HTH

Bob
 
P

puiuluipui

Hi Bob, i have some problems with your code.
=IF(INDEX(A1:L1,MATCH(9^9,A2:L2)+1)="","No more
rates",INDEX(A1:L1,MATCH(9^9,A2:L2)+1))

If A3 is empty, then the code display #N/A
For the rest of the cells, the code is working just fine.
If A3 has numbers, i need the code to display rate 2. instead of rate 2, the
code display #N/A.
Can you help me with this one?
Thanks!
 
B

Bob Phillips

Is this better?

=IF(A2="",A1,IF(INDEX(A1:L1,MATCH(9^9,A2:L2)+1)="","No more
rates",INDEX(A1:L1,MATCH(9^9,A2:L2)+1)))
 
P

puiuluipui

Beautiful.
Thanks!

Bob Phillips said:
Is this better?

=IF(A2="",A1,IF(INDEX(A1:L1,MATCH(9^9,A2:L2)+1)="","No more
rates",INDEX(A1:L1,MATCH(9^9,A2:L2)+1)))

--
__________________________________
HTH

Bob
 

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

Similar Threads

If value, then display underneath value 3
if, then display next cell 4
If cell not empty, then display from range 7
If, then display 9
Date problem 0
Formula help 1
Lookup 10
IF functions 2

Top