Formula Error

O_Junior · Nov 3, 2014

I'm trying to do this formula: =IF(A7:A1000="RL1";IF(G7:G1000=TODAY();SUM(I7:I1000);"");"") but i keep getting an error. My guess is that =TODAY() is wrong, but i'm not sure. Could someone help me?

O_Junior · Nov 3, 2014

Aladin Akyurek said:
1) Do you see { and } around the formula on the formula bar?

2) Since the formula was crafted after your own effort, can you perhaps describe in words what you intended to calculate with that?

All right, i'm gonna try to explain.
On the Cell A7, the user must choose from a drop-down menu which "RL" (which is a machine) he wants to requisite the materials he needs. Then, in cell B7 he input the number from the production order, which through some VBA magic, automatically fill the date in cell G7. Then, he input the material code in cell C/D7, then the quantity in cell E7, and in cell H7, how many pieces per hour he can produce. In the cell I7, i made a calcule to tell the user how many hours its gonna take him to finish that order. In cell K/L 3/4/5 (merged cells), i want to sum the cells which represent "RL1" or "RL2" etc.. But only as of today, cause its gonna be changing, well, everyday of course. I hope i made myself clear, and sorry for my bad English, i'm from Brazil.

Aladin Akyurek · Nov 3, 2014

O_Junior said:
All right, i'm gonna try to explain.
On the Cell A7, the user must choose from a drop-down menu which "RL" (which is a machine) he wants to requisite the materials he needs. Then, in cell B7 he input the number from the production order, which through some VBA magic, automatically fill the date in cell G7. Then, he input the material code in cell C/D7, then the quantity in cell E7, and in cell H7, how many pieces per hour he can produce. In the cell I7, i made a calcule to tell the user how many hours its gonna take him to finish that order. In cell K/L 3/4/5 (merged cells), i want to sum the cells which represent "RL1" or "RL2" etc.. But only as of today, cause its gonna be changing, well, everyday of course. I hope i made myself clear, and sorry for my bad English, i'm from Brazil.

Merged cells are never a good idea. Try to unmerge. Then try to run the fofrmula if that covers tyour intent. If not try rather post a small sample along with the result you want to see.

O_Junior · Nov 3, 2014

Aladin Akyurek said:
Merged cells are never a good idea. Try to unmerge. Then try to run the fofrmula if that covers tyour intent. If not try rather post a small sample along with the result you want to see.

Sorry but, how do i post a small sample?

Edit: Here is a link to a screenshot of my sheet https://www.dropbox.com/s/zqv2jnqzrwp8pbw/Capture1.JPG?dl=0

Aladin Akyurek · Nov 4, 2014

O_Junior said:
Sorry but, how do i post a small sample?

Edit: Here is a link to a screenshot of my sheet https://www.dropbox.com/s/zqv2jnqzrwp8pbw/Capture1.JPG?dl=0

Images require retyping in most cases. Try rather one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste.

O_Junior · Nov 4, 2014

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

1

Solicitao de Ordens das RL's - Bloco 66

Tempo Total de Produo

2

RL1

RL2

RL3

RL4

RL5

3

4

5

6

Mesa

Nmero da Ordem

Cdigo do Material

Quantidade

Turno

Data

Peas / Hora

Durao da Ordem

Status Almoxarifado

7

8

9

10

11

12

13

14

15

Sheet1

Cell Formulas
Range	Formula
I7	=IF(E7="","",(E7/H7)/1440)
I8	=IF(E8="","",(E8/H8)/1440)
I9	=IF(E9="","",(E9/H9)/1440)
I10	=IF(E10="","",(E10/H10)/1440)
I11	=IF(E11="","",(E11/H11)/1440)
I12	=IF(E12="","",(E12/H12)/1440)
I13	=IF(E13="","",(E13/H13)/1440)
I14	=IF(E14="","",(E14/H14)/1440)
I15	=IF(E15="","",(E15/H15)/1440)

O_Junior · Nov 4, 2014

Sorry for the mess, and for the Portuguese.

Andrew Poulsom · Nov 5, 2014

There's no data there, so how do you expect a formula to return other than zero? Post some data and the expected results.

Marcelo Branco · Nov 5, 2014

Aladin and Andrew

I received by email a simplified copy of o.Junior's workbook. I noted that the Worksheet_Change event used to insert the date/time in column G had some problems

I changed it to

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    
    If Not Intersect(Target, Range("B7:B1000")) Is Nothing Then
        If Target <> "" Then
            Target.Offset(0, 5) = Now()
        Else
            Target.Offset(0, 5) = ""
        End If
    End If
    
End Sub

I also changed the formula in column I to
=IF(OR(E7="",H7=""),"",(E7/H7)/24)

and the format of this column to [hh]:mm

In K3 i put the formula and used format [hh]:mm
=SUMPRODUCT(--($A$7:$A$1000="RL1"),--(INT($G$7:$G$1000)=TODAY()),$I$7:$I$1000)

Same idea in M3 and O3

I got

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A

[/TD]
[TD]

B

[/TD]
[TD]

C

[/TD]
[TD]

D

[/TD]
[TD]

E

[/TD]
[TD]

F

[/TD]
[TD]

G

[/TD]
[TD]

H

[/TD]
[TD]

I

[/TD]
[TD]

J

[/TD]
[TD]

K

[/TD]
[TD]

L

[/TD]
[TD]

M

[/TD]
[TD]

N

[/TD]
[TD]

O

[/TD]
[TD]

P

[/TD]
[TD]

Q

[/TD]
[TD]

R

[/TD]
[TD]

S

[/TD]
[TD]

T

[/TD]
[/TR]
[TR]
[TD]

1

[/TD]
[TD]

Solicitação de Ordens das RL's - Bloco 66

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Tempo Total de Produção

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

RL1

[/TD]
[TD][/TD]
[TD]

RL2

[/TD]
[TD][/TD]
[TD]

RL3

[/TD]
[TD][/TD]
[TD]

RL4

[/TD]
[TD][/TD]
[TD]

RL5

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

01:45

[/TD]
[TD][/TD]
[TD]

02:00

[/TD]
[TD][/TD]
[TD]

02:51

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6

[/TD]
[TD]

Mesa

[/TD]
[TD]

Número da Ordem

[/TD]
[TD]

Código do Material

[/TD]
[TD][/TD]
[TD]

Quantidade

[/TD]
[TD]

Turno

[/TD]
[TD]

Data

[/TD]
[TD]

Peças / Hora

[/TD]
[TD]

Duração da Ordem (hh:mm)

[/TD]
[TD]

Status Almoxarifado

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7

[/TD]
[TD]

RL1

[/TD]
[TD]

1

[/TD]
[TD]

cod1

[/TD]
[TD][/TD]
[TD]

1000

[/TD]
[TD]

100

[/TD]
[TD]

05/11/2014 10:52

[/TD]
[TD]

1000

[/TD]
[TD]

01:00

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8

[/TD]
[TD]

RL1

[/TD]
[TD]

2

[/TD]
[TD]

cod2

[/TD]
[TD][/TD]
[TD]

250

[/TD]
[TD]

300

[/TD]
[TD]

05/11/2014 10:52

[/TD]
[TD]

1000

[/TD]
[TD]

00:15

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9

[/TD]
[TD]

RL1

[/TD]
[TD]

3

[/TD]
[TD]

cod3

[/TD]
[TD][/TD]
[TD]

250

[/TD]
[TD]

200

[/TD]
[TD]

05/11/2014 10:52

[/TD]
[TD]

500

[/TD]
[TD]

00:30

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10

[/TD]
[TD]

RL2

[/TD]
[TD]

4

[/TD]
[TD]

cod1

[/TD]
[TD][/TD]
[TD]

1500

[/TD]
[TD]

200

[/TD]
[TD]

05/11/2014 10:52

[/TD]
[TD]

1000

[/TD]
[TD]

01:30

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11

[/TD]
[TD]

RL2

[/TD]
[TD]

5

[/TD]
[TD]

cod3

[/TD]
[TD][/TD]
[TD]

500

[/TD]
[TD]

300

[/TD]
[TD]

05/11/2014 10:52

[/TD]
[TD]

1000

[/TD]
[TD]

00:30

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12

[/TD]
[TD]

RL3

[/TD]
[TD]

6

[/TD]
[TD]

cod2

[/TD]
[TD][/TD]
[TD]

450

[/TD]
[TD]

300

[/TD]
[TD]

05/11/2014 10:52

[/TD]
[TD]

1000

[/TD]
[TD]

00:27

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13

[/TD]
[TD]

RL3

[/TD]
[TD]

7

[/TD]
[TD]

cod9

[/TD]
[TD][/TD]
[TD]

1200

[/TD]
[TD]

200

[/TD]
[TD]

05/11/2014 10:53

[/TD]
[TD]

500

[/TD]
[TD]

02:24

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I sent the test workbook to O_Junior and let's wait to see if everything is ok to him.

Any others ideas?

M.

O_Junior · Nov 5, 2014

Marcelo Branco said:
Aladin and Andrew

I received by email a simplified copy of o.Junior's workbook. I noted that the Worksheet_Change event used to insert the date/time in column G had some problems

I changed it to

Code:

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Range("B7:B1000")) Is Nothing Then If Target <> "" Then Target.Offset(0, 5) = Now() Else Target.Offset(0, 5) = "" End If End If End Sub

I also changed the formula in column I to
=IF(OR(E7="",H7=""),"",(E7/H7)/24)

and the format of this column to [hh]:mm

In K3 i put the formula and used format [hh]:mm
=SUMPRODUCT(--($A$7:$A$1000="RL1"),--(INT($G$7:$G$1000)=TODAY()),$I$7:$I$1000)

Same idea in M3 and O3

I got

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]
A
[/TD]
[TD]
B
[/TD]
[TD]
C
[/TD]
[TD]
D
[/TD]
[TD]
E
[/TD]
[TD]
F
[/TD]
[TD]
G
[/TD]
[TD]
H
[/TD]
[TD]
I
[/TD]
[TD]
J
[/TD]
[TD]
K
[/TD]
[TD]
L
[/TD]
[TD]
M
[/TD]
[TD]
N
[/TD]
[TD]
O
[/TD]
[TD]
P
[/TD]
[TD]
Q
[/TD]
[TD]
R
[/TD]
[TD]
S
[/TD]
[TD]
T
[/TD]
[/TR]
[TR]
[TD]
1
[/TD]
[TD]
Solicitação de Ordens das RL's - Bloco 66
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
Tempo Total de Produção
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
RL1
[/TD]
[TD][/TD]
[TD]
RL2
[/TD]
[TD][/TD]
[TD]
RL3
[/TD]
[TD][/TD]
[TD]
RL4
[/TD]
[TD][/TD]
[TD]
RL5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
01:45
[/TD]
[TD][/TD]
[TD]
02:00
[/TD]
[TD][/TD]
[TD]
02:51
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
6
[/TD]
[TD]
Mesa
[/TD]
[TD]
Número da Ordem
[/TD]
[TD]
Código do Material
[/TD]
[TD][/TD]
[TD]
Quantidade
[/TD]
[TD]
Turno
[/TD]
[TD]
Data
[/TD]
[TD]
Peças / Hora
[/TD]
[TD]
Duração da Ordem (hh:mm)
[/TD]
[TD]
Status Almoxarifado
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
7
[/TD]
[TD]
RL1
[/TD]
[TD]
1
[/TD]
[TD]
cod1
[/TD]
[TD][/TD]
[TD]
1000
[/TD]
[TD]
100
[/TD]
[TD]
05/11/2014 10:52
[/TD]
[TD]
1000
[/TD]
[TD]
01:00
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
8
[/TD]
[TD]
RL1
[/TD]
[TD]
2
[/TD]
[TD]
cod2
[/TD]
[TD][/TD]
[TD]
250
[/TD]
[TD]
300
[/TD]
[TD]
05/11/2014 10:52
[/TD]
[TD]
1000
[/TD]
[TD]
00:15
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
9
[/TD]
[TD]
RL1
[/TD]
[TD]
3
[/TD]
[TD]
cod3
[/TD]
[TD][/TD]
[TD]
250
[/TD]
[TD]
200
[/TD]
[TD]
05/11/2014 10:52
[/TD]
[TD]
500
[/TD]
[TD]
00:30
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
10
[/TD]
[TD]
RL2
[/TD]
[TD]
4
[/TD]
[TD]
cod1
[/TD]
[TD][/TD]
[TD]
1500
[/TD]
[TD]
200
[/TD]
[TD]
05/11/2014 10:52
[/TD]
[TD]
1000
[/TD]
[TD]
01:30
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
11
[/TD]
[TD]
RL2
[/TD]
[TD]
5
[/TD]
[TD]
cod3
[/TD]
[TD][/TD]
[TD]
500
[/TD]
[TD]
300
[/TD]
[TD]
05/11/2014 10:52
[/TD]
[TD]
1000
[/TD]
[TD]
00:30
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
12
[/TD]
[TD]
RL3
[/TD]
[TD]
6
[/TD]
[TD]
cod2
[/TD]
[TD][/TD]
[TD]
450
[/TD]
[TD]
300
[/TD]
[TD]
05/11/2014 10:52
[/TD]
[TD]
1000
[/TD]
[TD]
00:27
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
13
[/TD]
[TD]
RL3
[/TD]
[TD]
7
[/TD]
[TD]
cod9
[/TD]
[TD][/TD]
[TD]
1200
[/TD]
[TD]
200
[/TD]
[TD]
05/11/2014 10:53
[/TD]
[TD]
500
[/TD]
[TD]
02:24
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I sent the test workbook to O_Junior and let's wait to see if everything is ok to him.

Any others ideas?

M.

Thats is exactly what i needed! Thank you so much!

Marcelo Branco · Nov 5, 2014

O_Junior said:
Thats is exactly what i needed! Thank you so much!

You are welcome. Glad for helping

M.

Formula Error

O_Junior

New Member

O_Junior

New Member

Aladin Akyurek

MrExcel MVP

O_Junior

New Member

Aladin Akyurek

MrExcel MVP

O_Junior

New Member

O_Junior

New Member

Andrew Poulsom

MrExcel MVP

Marcelo Branco

MrExcel MVP

O_Junior

New Member

Marcelo Branco

MrExcel MVP

Similar threads

Share this page

Formula Error

New Member

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

New Member

MrExcel MVP

MrExcel MVP

New Member

MrExcel MVP

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock