# Re: If empty, then display message

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

"puiuluipui" <puiuluipui@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A888958C-8377-480A-834D-34944C3D7638@xxxxxxxxxxxxxxxx
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!

"Bob Phillips" wrote:

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

"puiuluipui" <puiuluipui@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C40D5F77-E7D4-4802-B9D9-9649F0DDE3B7@xxxxxxxxxxxxxxxx
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!

"Bob Phillips" wrote:

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

--
__________________________________
HTH

Bob

"puiuluipui" <puiuluipui@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C5DA10E7-C41F-454E-80C2-4014946C81AF@xxxxxxxxxxxxxxxx
Hi Jacob, i added "+1" to display next rate, and i changed ranges.
A1:L1= rates
A3:L3= billings nr

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!

"Jacob Skaria" wrote:

Try the below
=INDEX(A1:L1,MATCH(9^9,A2:L2))

If this post helps click Yes
---------------
Jacob Skaria

"puiuluipui" wrote:

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!

"Dan" wrote:

Can you provide a bit more info. Exactly what data is in the
ranges
A1:L1 &
A2:L2?

"puiuluipui" wrote:

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!

.

## Relevant Pages

• Re: If empty, then display message
... If A3 is empty, then the code display #N/A ... I have 2 ranges: A1:L1 and A3:L3 ... remaining, but the same range, the code will find an empty cell at ...
(microsoft.public.excel.misc)
• Re: If empty, then display message
... "Bob Phillips" wrote: ... If A3 is empty, then the code display #N/A ... remaining, but the same range, the code will find an empty cell ...
(microsoft.public.excel.misc)
• RE: OWC11 Pivot Table has problem when i use LastChild functions
... custom rollup formula or if i set pivot table control always display empty ... *The data/Cube is valid as it work fine in Excel 2003 Pivot Table. ... Microsoft Online Community Support ...
(microsoft.public.office.developer.web.components)
• Re: BLANK OE-6 eMail text area in todays and any previous days email
... by from my ISP, then the text area of the message is completely empty, so I ... received emails and thir contents are displayed in each one's message window ... I cannot find any "option" that affects the display of message text. ...
(microsoft.public.windows.inetexplorer.ie6_outlookexpress)
• Re: If empty, then display message
... the last 2 cells are empty) ... I need in the code to display next rate. ... remaining, but the same range, the code will find an empty cell at some ... Can you provide a bit more info. Exactly what data is in the ranges ...
(microsoft.public.excel.misc)