Excel formula needed

Question

Hi - (Excel 2000, Microsoft XP) I have anspreadsheet with two worksheets, Sheet 1 is titled "SAM", Sheet 2 is titled "Sales Point". Both sheets have identical columns, I would like to compare the data in column B on both worksheets which both have the same Heading of "Order Number" then highlight cells where the data is different. My skill level withis about a 5. Can you provide me with a formula?

Answer

It would require a macro to highlight the unmatched cell directly in column B. But I think it is almost as good to just have an adjacent cell somewhere in the same row highlight to give you an indication that the value in column B doesn't match. You can do this as follows:

1. In row 2 of an available (empty) column of Sales Point enter the formula

 =(B2=SAM!B2)

This should immediately return a TRUE/FALSE value, TRUE if the value in B2 is the same on the SAM sheet, FALSE if not.

2. Copy (autofill) this formula down as many rows as necessary to cover all your data.

You should now be able to see where your unmatching data values are. This is perhaps not as visually noticeable as you would like it to be, so let's add some color.

3. Select all the cells containing the new formula.

4. Go to Format > Conditional Formatting.

5. In the box to the right of "Cell Value Is" use the dropdown to select "Equal To". In the box to the right of that enter the text "FALSE" (without the quotes).

6. Click the Format button and then the Patterns tab. Pick a nice bright cell shading color and click OK.

That should do it. You could, it you desire, set the text color for that column of cells to white so that the TRUE values would not be visible and distracting.

Question

I need to set up a spreadsheet that will calculate my hours of work for each project and the amount earned for my time. I have input my start time in cell B, end time in cell C. I need totals hours worked and cost. Pay rate is $20 per hour. Can you help me set up a formula for this?

Answer

Jennifer,

If the end time in each row will be within the same day, then

B2: Start time C2: End Time D2: =if(C2="","",c2-B2) format cell as time

E2: =if(c2="","",(24*D2)*20) format as currency

Now select D2:E2 and drag fill down columns D:E

G2: Total Hours: H2: =sum(D:D) use a custom number format of [hh]:mm

G3: Total Cost: H3: =sum(E:E) format as currency

if that isn't what you want send me a sample workbook to twogilvy@msn.com indicating where you want what.