Please Help Again-Condition Formula

[Kareinia]

Can I please get additional help with the post that I posted earlier.
My first question was
If G37=daily, then F37 cannot be greater then 1. If it is greater then one,
then an error message will show in K37.

I received a correct answer for that question..
=IF(AND(G37="Daily",F37<=1),"OK",Error")

I didn't think that some items will be based on hourly rates and I will not
know what rolls will be that until the look up code picks up if it's a daily
or hourly item that I'm billing. (I have a billing codes that are based on
different rates that are picked up on another sheet, based on the code number
I use)

Can the formula have that if it's daily, that it will show the error if over
1, and if hourly it will not show an error if it's over 1?
Lordy mercy, I'm having a hard time even saying what I need it to show....I
hope someone will understand what I'm needing and respond....

Instead of lost in excel, I'm going to say dumb in excel.....
I would appreciate the help....
Thanks again.
Kareinia

 

[Rick Rothstein \(MVP - VB\)]

I'm not sure if I have understood your question completely or not. Does this
do what you are looking for?

=IF(AND(G37="Daily",F37>1),"Error","OK")

Rick

 

[Kareinia]

That would work if Daily would only come up on certain rolls...But if I put
in a charge code that will have an hourly rate, I would need that roll to be
okay...
It looks like this on my spread sheet
F37
G37 K37
classification #of Hours/Day Hours or day
Show Error
Dozer 10 Hours
Air Compressor 1 Day


I need it to show that I made a mistake if I put more then 1 in the #of
hours/day column, if the classificate is charge by daily rate.
I catch errors where I am keying that I put a 10 in where it should be 1 for
a daily rate...

I need to copy the condition down for several rolls that will change from
either an item that is based on hourly or items that will be based on daily.

If I put 10 on a classification that should be daily, I actually bill for 10
days...I was wanting a flag that would come up if I made that mistake.

I appreciate you helping me....I've tried to come up with it myself, but no
luck....

 

[Rick Rothstein \(MVP - VB\)]

Given the indicated purpose behind your question, perhaps you might consider
this alternative approach. First, though, I need to say your posted sample
layout is a little confusing... you seem to be referencing a specific row
(37), but yet you appear to have multiple rows of data (as would be
expected), plus your spacing for these column designation are spaced oddly
on my newsreader. So, assuming your data actually starts at Row 2 (assuming
Row 1 is a header row) and that your "# of Hours/Day" column is Column G and
that your "Hours or day" column is Column K, do the following...

1) As long as the header text in K1 will not be the single word "Day" (or
"Daily" depending on which term you are actually using in that column when
not Hours), then select the entire Column G.

2) Click Format/Conditional Formatting on Excel's menu bar.

3) Select "Formula Is" from the first drop-down box in the dialog box that
appears.

4) Put this formula in the second field (next to the Formula Is
drop-down)...

=AND(G1>1,H1="Day")

Note: Use "Daily" instead of "Day" if that is what you put in Column K

5) Click the Format button and then click the Patterns tab on the dialog
panel that appears. Select a pale color from the grid of colors (I would
suggest the pale red) and then OK your way back to the worksheet.

From now on, if "Day" (or "Daily", again, depending on what it is you
actually enter in Column K) appears in Column K and you put an entry greater
than 1 in Column G... that "greater than one" number will be flagged in the
color you picked in Step 5 alerting you to the error visually.

Rick

 

[Rick Rothstein \(MVP - VB\)]

4) Put this formula in the second field (next to the Formula Is

drop-down)...

=AND(G1>1,H1="Day")

Note: Use "Daily" instead of "Day" if that is what you put in Column K

Damn! I forgot to change the column reference from my test case... the above
formula should have been this...

=AND(G1>1,K1="Day")

Sorry for any confusion that may have caused.

Rick

 

[Kareinia]

Thanks for you help!

Damn! I forgot to change the column reference from my test case... the above
formula should have been this...

=AND(G1>1,K1="Day")

Sorry for any confusion that may have caused.

Rick