software

I have di rows:

02/01/1997 BIANCHI Automobili
02/01/1997 BIANCHI Barche
05/01/1997 ROSSI Foto
05/01/1997 ROSSI Video
07/01/1997 VERDI Spettacolo
08/01/1997 NERI Modellismo
10/01/1997 VERDI Moda
12/01/1997 VERDI Viaggi
13/01/1997 ROSSI SportI need a way to search by date = quot;05/01/1997quot; and costumer = quot;ROSSIquot; and get
as result in a cell a string with quot;Foto, Videoquot;
is it possible??? Please help me!
If you don't mind having the values in separate cells, assuming that
A2:C10 contains the data, let E2 contain the date of interest, such as
05/01/1997, let F2 contain the costumer of interest, such as ROSSI, and
then try the following...

G2:

=SUMPRODUCT(--($A$2:$A$10=E2),--($B$2:$B$10=F2))

H2, copied across:

=IF(COLUMNS($H2:H2)lt;=$G2,INDEX($C$2:$C$10,SMALL(IF ($A$2:$A$10=$E2,IF($B$2
:$B$10=$F2,ROW($C$3:$C$10)-ROW($C$3) 1)),COLUMNS($H2:H2))),quot;quot;)

....confirmed with CONTROL SHIFT ENTER, not just ENTER.

Hope this helps!

In article gt;,
quot;LuckyStarquot; gt; wrote:

gt; I have di rows:
gt;
gt; 02/01/1997 BIANCHI Automobili
gt; 02/01/1997 BIANCHI Barche
gt; 05/01/1997 ROSSI Foto
gt; 05/01/1997 ROSSI Video
gt; 07/01/1997 VERDI Spettacolo
gt; 08/01/1997 NERI Modellismo
gt; 10/01/1997 VERDI Moda
gt; 12/01/1997 VERDI Viaggi
gt; 13/01/1997 ROSSI Sport
gt;
gt;
gt; I need a way to search by date = quot;05/01/1997quot; and costumer = quot;ROSSIquot; and get
gt; as result in a cell a string with quot;Foto, Videoquot;
gt; is it possible??? Please help me!

Deleting Range after changing Cell
The input in cell C4 is restricted (by validation list) p.e
East, West, South, North
(Cell C11.. c19 are also restricted(val. list) but the list
depends on the value in C4.)

C4=East C11..C19 p.e Green, Yellow, Black
C4=West C11..C19 p.e Brown, Red , Orange
C4=South C11..C19 p.e White, Purple, Blue
etc......
When C4 is changed I want to delete the values C11.. C19.

This to prevent that C4 is changed after filling in C11..C19 and
the values don't match anymore

p.e. South (c4) and Purple(C11) are chosen and after changing C4
in West it doesn't match anymore.

How can I prevent the above (in VBA)Peter,

A simple sheet Macro like:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Address lt;gt; Range(quot;C4quot;).Address Then Exit Sub
Range(quot;C11:C19quot;).ClearContents

End Sub

entered in the Worksheet Module should do what you are asking for.

--
HTH

Sandy
with @tiscali.co.uk

quot;Peter55quot; gt; wrote in message
.. .
gt; Deleting Range after changing Cell
gt; The input in cell C4 is restricted (by validation list) p.e
gt; East, West, South, North
gt; (Cell C11.. c19 are also restricted(val. list) but the list
gt; depends on the value in C4.)
gt;
gt; C4=East C11..C19 p.e Green, Yellow, Black
gt; C4=West C11..C19 p.e Brown, Red , Orange
gt; C4=South C11..C19 p.e White, Purple, Blue
gt; etc......
gt; When C4 is changed I want to delete the values C11.. C19.
gt;
gt; This to prevent that C4 is changed after filling in C11..C19 and
gt; the values don't match anymore
gt;
gt; p.e. South (c4) and Purple(C11) are chosen and after changing C4
gt; in West it doesn't match anymore.
gt;
gt; How can I prevent the above (in VBA)
gt;

Thanks sandy,

That was the Macro I was looking for
Peter,

A simple sheet Macro like:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Address lt;gt; Range(quot;C4quot;).Address Then Exit Sub
Range(quot;C11:C19quot;).ClearContents

End Sub

entered in the Worksheet Module should do what you are asking for.