Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
If empty, then display message
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
If empty, then display message
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
If empty, then display message
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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
If empty, then display message
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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
If empty, then display message
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! "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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
If empty, then display message
=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" wrote in message ... 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! "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! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
If empty, then display message
If using Excel 2007, you can use
=IFERROR(INDEX(A1:L1,MATCH(9^9,A3:L3)+1),"No more rates") -- __________________________________ HTH Bob "puiuluipui" wrote in message ... 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! "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! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
If empty, then display message
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" wrote in message ... 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! "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! |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
If empty, then display message
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 using Excel 2007, you can use =IFERROR(INDEX(A1:L1,MATCH(9^9,A3:L3)+1),"No more rates") -- __________________________________ HTH Bob "puiuluipui" wrote in message ... 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! "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! |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
If empty, then display message
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" wrote in message ... 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" wrote in message ... 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! "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! |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
If empty, then display message
Yes, this is exactly what i want.
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" wrote in message ... 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" wrote in message ... 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! "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! |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
If empty, then display message
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" wrote in message ... 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" wrote in message ... 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! "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! |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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! "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! |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
If empty, then display message
Beautiful.
Thanks! "Bob Phillips" wrote: 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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! "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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Message: 'Cannot empty clipboard' | Excel Discussion (Misc queries) | |||
Message: Cannot empty clipboard | Excel Discussion (Misc queries) | |||
Error message: Cannot empty the clipboard. | Excel Discussion (Misc queries) | |||
Error Message: Cannot Empty the Clipboard | New Users to Excel | |||
Error Message "Cannot Empty Clipboard" | Setting up and Configuration of Excel |