Error in IF? (Microsoft Excel 2010)

Hello,

New User here so please be gentle

I was given the attached spreadsheet due to an error in column E and F of Sheet "Equipment Hours".
The sheet used to work and now it doesn't. (Sadly I can't get a copy of the document when it was working!!)

Either way, the IF statement works for only a few of the lines (I have reduced the full sheet for ease of problem solving).
I have read through it and the only issue I could find is the range specified is too short:

Forumla is:
=MAX(IF('1SAP Data'!$A$1:$A$940=D2,'1SAP Data'!$B$1:$B$940))
Only looks from 1-940 in Column A of sheet '1SAP Data'.

Even when this is corrected to say 8000 cells or even the complete column - the error remains.

I also thought it might be an error caused by incorrect formatting of the cells containing numbers (currently they are mixed format as general and number). This also didn't resolve anything.

Basically the user runs a report and inserts new data into Sheet '1SAP Data' by adding to the bottom (the new 'Measurement Point" value increases each time).
And they are sorted in ascending order to help the IF statement work - however

Note - some of the measure documents the IF statement looks for don't exist in the '1SAP Data' table - so these would of course return an error. But not all of them?!

What else am I missing?
Help greatly appreciated!!

Hi,

For got use Ctrl+Shift+Enter after copying the formula try the said combination of keys to get the desired results

Punnam

HI JoshEvo ,

Was your question answered , or need more clarification ?

Punnam

Hi Punnam,

I believe it is solved now. Will mark the thread as such.

Thanks to both of you! (boopathiraja as well)

Cheers,
Josh

Hi JoshEvo ,

The tanks in Excel forum can be done by adding reputation (If you received the required Help)

Punnam

Now done, thanks again.