I cannot figure out how to make an if function (or macro?) which returns e.g.
the value false or error if the data in a certain cell already exists in
another cell in the same row.

I have made a table which shows which workers are working which day and at
which machine. The days are in the left column and the machines in the top
row. The workers(using last names) are in the cells inside the table. The
function should check or prevent that the same worker is working at two
different machines at the same time. In other words the same data cannot be
in two different cells in the same row. Should I use some kind of if
function or would a macro work better?

=IF(COUNTIF(A:A,A1)1,"ERROR","")

HTH
--
AP

"Wilhelm" a écrit dans le message de
news: ...
I cannot figure out how to make an if function (or macro?) which returns
e.g.
the value false or error if the data in a certain cell already exists in
another cell in the same row.

I have made a table which shows which workers are working which day and at
which machine. The days are in the left column and the machines in the top
row. The workers(using last names) are in the cells inside the table. The
function should check or prevent that the same worker is working at two
different machines at the same time. In other words the same data cannot
be
in two different cells in the same row. Should I use some kind of if
function or would a macro work better?

I suggest that you use Data Validation (menu DataValidation), with an allow
type of Custom, and a formula of =COUNTIF(2:2,B2)=0

This way, the error is trapped on input.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Wilhelm" wrote in message
...
I cannot figure out how to make an if function (or macro?) which returns
e.g.
the value false or error if the data in a certain cell already exists in
another cell in the same row.

I have made a table which shows which workers are working which day and at
which machine. The days are in the left column and the machines in the top
row. The workers(using last names) are in the cells inside the table. The
function should check or prevent that the same worker is working at two
different machines at the same time. In other words the same data cannot
be
in two different cells in the same row. Should I use some kind of if
function or would a macro work better?

Thank you for your help,

Unfortunately that kind of function wasnt exactly what I meant. I need a
function which prevents me from writing the same name in a certain cell by
returning the text €œERROR€� or €œFALSE€� in the SAME cell which I wrote the name
in. In other words the function should somehow be hidden so that its
possible to change the name in the cell without having to change the
function. The table is large and the names are constantly being changed. Is
this kind of function even possible using Excel. I would be grateful for any
help you can give me.


"Ardus Petus" wrote:

=IF(COUNTIF(A:A,A1)1,"ERROR","")

HTH
--
AP

"Wilhelm" a écrit dans le message de
news: ...
I cannot figure out how to make an if function (or macro?) which returns
e.g.
the value false or error if the data in a certain cell already exists in
another cell in the same row.

I have made a table which shows which workers are working which day and at
which machine. The days are in the left column and the machines in the top
row. The workers(using last names) are in the cells inside the table. The
function should check or prevent that the same worker is working at two
different machines at the same time. In other words the same data cannot
be
in two different cells in the same row. Should I use some kind of if
function or would a macro work better?

Wilhelm,

there are more qualified people here to give more specifics, but I
suggest you start looking at the worksheet_selectionchange event. This
can search the row/column and place an "ERROR" if it meets your
criteria. It will be triggered each time you make a change to a
particular targeted area.

Regards,

andym


Wilhelm wrote:
Thank you for your help,

Unfortunately that kind of function wasn't exactly what I meant. I need a
function which prevents me from writing the same name in a certain cell by
returning the text "ERROR" or "FALSE" in the SAME cell which I wrote the name
in. In other words the function should somehow be hidden so that it's
possible to change the name in the cell without having to change the
function. The table is large and the names are constantly being changed. Is
this kind of function even possible using Excel. I would be grateful for any
help you can give me.


"Ardus Petus" wrote:

=IF(COUNTIF(A:A,A1)1,"ERROR","")

HTH
--
AP

"Wilhelm" a écrit dans le message de
news: ...
I cannot figure out how to make an if function (or macro?) which returns
e.g.
the value false or error if the data in a certain cell already exists in
another cell in the same row.

I have made a table which shows which workers are working which day and at
which machine. The days are in the left column and the machines in the top
row. The workers(using last names) are in the cells inside the table. The
function should check or prevent that the same worker is working at two
different machines at the same time. In other words the same data cannot
be
in two different cells in the same row. Should I use some kind of if
function or would a macro work better?

Thank you,

That was exactly what I was looking for.


"Bob Phillips" wrote:

I suggest that you use Data Validation (menu DataValidation), with an allow
type of Custom, and a formula of =COUNTIF(2:2,B2)=0

This way, the error is trapped on input.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Wilhelm" wrote in message
...
I cannot figure out how to make an if function (or macro?) which returns
e.g.
the value false or error if the data in a certain cell already exists in
another cell in the same row.

I have made a table which shows which workers are working which day and at
which machine. The days are in the left column and the machines in the top
row. The workers(using last names) are in the cells inside the table. The
function should check or prevent that the same worker is working at two
different machines at the same time. In other words the same data cannot
be
in two different cells in the same row. Should I use some kind of if
function or would a macro work better?