Check if cell range has value and return text

[Postlki1]

Hi again!

Still working on my Travel Expense Form...

I have tried solving this, but it wonīt work. I am afraid that it might be a job for Visual Basic again, which I donīt know at all...

Here we go:

If cell range F7:F36 (meaning any of those cells) is empty do nothing
but if any cell in that range has a numeric value return "CHOOSE CURRENCY!"

I tried this, but it doesnīt do the trick.
=IF(F7:F36="","","CHOOSE CURRENCY!")

Any ideas?

Thanks in advance,
Kim

[HSV]

Hello Kim,

You mean.
For text and numeric values

=IF(COUNTA(F7:F36)=0,"","CHOOSE CURRENCY!")  

Or only numeric value.

=IF(COUNT(F7:F36)=0,"","CHOOSE CURRENCY!")  

[Postlki1]

Cool, thanks for the quick answer! It works *happy*. Gee, what I love the internet and all those helpful people using it.

[Postlki1]

Ok, just to step it up a notch...

a cell in the defined range F7:F36 has a number (an aomount), and as a result the screamy red text CHOOSE CURRENCY! is popping up.
the user goes to the G column, opens the drop-down and chooses a currency...
how do I get the CHOOSE CURRENCY text to disappear again?

any formula for that?

[HSV]

You mean like this?

=IF(AND(COUNT(F7:F36)>0,COUNT(G7:G36) >0),"","CHOOSE CURRENCY!")  

[Postlki1]

Dear Harry,

Thanks for that one. The AND formula and I are not on best terms.

I tried the formula. It does not really do the trick. Since the G column has a drop-down list with text (currency letters), it will not take away the warning CHOOSE CURRENCY! even if the drop-down is used, and the cell populated. Maybe I should mention that the drop-down list has one empty cell, so when the user is opening the excel file, the G7:G36 will be empty per default. Hence the need to choose a currency.

If it would make it more easy, I would say the second condition (empty G-range) could be reduced to cell G7. After all, the warning is more of a reminder to use the roll down menu in column G when proceeding with the data registering of expenses. If the user will have followed the reminder once (in G7), hopefully it will become a habit for the rest of the document.

So - this is what should happen in cell A5:

F7:F36 is empty, return an empty cell
F7:F36 has any numeric value (most likely in F7), but NO text in G7 than warn "CHOOSE CURRENCY!"
F7:F36 has a numeric value AND G7 has text, then return an empty cell again

Hope this is detailed enough. Thanks for helping me figuring this formula out. I have tried quite a bit myself and did not get anywhere...

Greetings,
Kim

[HSV]

Hello Kim,

Would this help you?

=IF(AND(COUNT(F7:F36)=0,G7=""),"",IF(AND(COUNT(F7:F36)>0,G7=""),"CHOOSE CURRENCY!",""))  

[Postlki1]

Hi again Harry,

I have been quite busy this week, therefore the late reply. Thanks, the formula is working as it should.

kr,
Kim