#### NeedsHelp2

##### New Member
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

=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(--

(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

\$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

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
sheets

Does your workbook have 3 sheets to it?

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.

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.

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)?

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.

Replies
16
Views
503
Replies
3
Views
3K
Replies
13
Views
659
Replies
4
Views
163
Replies
0
Views
353

1,203,203
Messages
6,054,120
Members
444,703
Latest member
pinkyar23

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back