Formula No Longer Works - Please Help

[NeedsHelp2]

I'm in the process of creating a spreadsheet based on another one created by an ex work collegue. I've copied the formula from his original workbook and it no longer works even though it is exactly the same.

The formula is below, I've had to show it this way as if I copy into the forum as a whole, it loses some of the data. I've tried

Can anyone please help as I really need to use the formula.


=IF((SUMPRODUCT(--(Sheet3!$I$4:Sheet3!$I$31="Meal Relief"),--(INDEX

(Sheet3!$T$4:Sheet3!$Z$31,0,MATCH($D12,Sheet3!$T$1:Sheet3!$Z$1,0))

="Y"),Sheet3!$H$4:Sheet3!$H$31-Sheet3!$G$4:Sheet3!$G$31+(Sheet3!

$H$4:Sheet3!$H$31<Sheet3!$G$4:Sheet3!$G$31)))+(SUMPRODUCT(--

(Sheet3!$I$4:Sheet3!$I$31="Meal Relief Whilst Vehicle Un/Loaded"),--

(INDEX(Sheet3!$T$4:Sheet3!$Z$31,0,MATCH($D12,Sheet3!$T$1:Sheet3!

$Z$1,0))="Y"),Sheet3!$H$4:Sheet3!$H$31-Sheet3!$G$4:Sheet3!$G$31+

(Sheet3!$H$4:Sheet3!$H$31<Sheet3!$G$4:Sheet3!$G$31)))=0,"",

(SUMPRODUCT(--(Sheet3!$I$4:Sheet3!$I$31="Meal Relief"),

--(INDEX(Sheet3!$T$4:Sheet3!$Z$31,0,MATCH($D12,Sheet3!$T$1:Sheet3!

$Z$1,0))="Y"),Sheet3!$H$4:Sheet3!$H$31-Sheet3!$G$4:Sheet3!$G$31+

(Sheet3!$H$4:Sheet3!$H$31<Sheet3!$G$4:Sheet3!$G$31)))+

(SUMPRODUCT(--(Sheet3!$I$4:Sheet3!$I$31="Meal Relief Whilst Vehicle

Un/Loaded"),--(INDEX(Sheet3!$T$4:Sheet3!$Z$31,0,MATCH($D12,Sheet3!

$T$1:Sheet3!$Z$1,0))="Y"),Sheet3!$H$4:Sheet3!$H$31-Sheet3!

$G$4:Sheet3!$G$31+(Sheet3!$H$4:Sheet3!$H$31<Sheet3!$G$4:Sheet3!

$G$31))))


Thanks

 

[bam12]

sheets

Does your workbook have 3 sheets to it?

 

[NBVC]

Two things to check first:

1: Do those exact text strings exist in Sheet3, Column I of the new workbook?

2: If so, try pressing F2 while you're in the cell containing the formula and then holding down the CTRL + Shift and press Enter. You should see curly brackets appear around the formula.

Does this work?

If not, we can dig deeper.

 

[NeedsHelp2]

Bam12 There are more than three sheets and sheet3 does exist. Not sure if this makes any difference but in the original sheet3 was called sheet 3. In the new one the one named sheet3 is sheet5 looking in the VBA editor?

NBVC Curly brackets are now there but the error is still the same. Please note that in the original there are no curly brackets.

 

[NBVC]

Your last 2 lines don't make sense. Is that the way they appear in the formula?

Edit: Actually there are several places that you probably didn't copy the formula correctly. Try the click the Code button before and after copying the formula to the board in its entirety.


Are you just getting an error like #value, etc... or do you get a dialogue stating that the formula has errors (if so, what is that error message)?

 

[NeedsHelp2]

NBVC

Solved it I'm an idiot!

Went to make myself a cup of tea, came back and saw what I was doing wrong straight away.

Thanks for your help.