checking data before accepting input

  • Thread starter Thread starter J_J
  • Start date Start date
J

J_J

Hi,
I want to use Input Box's to enter data to cells C2:C, D2;D and E2:E row by
row for Sheet1. But I need to check if the info I input for cells (C2:C AND
D2:D both) does not exist in the region of B4:AY14 on Sheet2 (where the rows
represents info similar to C2:C and columns represent info similar to D2:D
on Sheet1) !. Hope I summerized it correctly.
How can I do it?. (With and without VBA)
TIA
J_J
 
Hi,

Sorry about my mistakes in the previous post. I think I wasn't very
sucessful giving a

clear view of my problem. Here is a much clearer description of my excel
problem...

-----------

I want to use Input Box's to enter data to columns C2:C, D2;D and E2:E row
by
row on Sheet1 (where the info on column C, D and E will hold the data for
"period",

"activity" and "trainer" respectively).

On Sheet2's A4:A column, I have a list of all possible "tariner" values of
Sheet1.
On Sheet2's B3:AY3 range, I have a list of all possible "period" values of
Sheet1.

Lastly on range B4:AY21 of Sheet2, I use VLOOKUP to display the "activity"
cell info for

the matching "period" and "trainer" values retrieved from Sheet1.

So far so good. No problems. I can manage that.

But before actually accepting and "writing" the data into the columns of
C2:C, D2:D and

E2:E as rows of new elements on Sheet1, I need to make sure that the info
I'll input on

Sheet1 for each row of cells ("period" & "activity" & "trainer" values)
there doesn't exist

another row with the same data trio.

Hope I summerized it correctly this time.

How can I do it?. (With and without VBA)
TIA

J_J
 
You could use conditional formatting to highlight the rows that are
duplicates. For example --

Select cells C2:E100 (C2 is the active cell)
Choose Format>Conditional Formatting
From the first dropdown, choose Formula Is
In the formula box, type:

=AND(COUNTA($C2:$E2)=3,
SUMPRODUCT(($C$2:$C$25=$C2)*($D$2:$D$25=$D2)*($E$2:$E$25=$E2))>1)

Click the Format button, and on the Patterns tab, choose a colour to
highlight the duplicates
Click OK, click OK
 
Thank you Debra,
If you can have a look to the same Q.&thread I on
"microsoft.public.excel.programming" NG, I am more interested in
"eliminating" duplicate entries. But thank you for your suggestions...
J_J
 
Back
Top