Please help me!

[LuckyStar]

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 Sport


I need a way to search by date = "05/01/1997" and costumer = "ROSSI" and get
as result in a cell a string with "Foto, Video"
is it possible??? Please help me!

 

[Domenic]

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)<=$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))),"")

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

Hope this helps!

 

[Peter55]

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)

 

[Sandy Mann]

Peter,

A simple sheet Macro like:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Address <> Range("C4").Address Then Exit Sub
Range("C11:C19").ClearContents

End Sub

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

--
HTH

Sandy
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Peter55]

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 <> Range("C4").Address Then Exit Sub
Range("C11:C19").ClearContents

End Sub

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