Auto-populate based on certain criteria?

[scoobz]

OK here goes...

I've made an excel document for stock/sales analysis. For example,
have made a download of raw data and put it a sheet named 'data'.
then have a sheet named 'overview' in which I use various vlooku
formulas to take information from the 'data' sheet.

Eg:

A3 = "product code" <---- manually entered

...then cells B3, C3 and D3 have vlookup formulas to take the produc
code entered in A5 and search for it in the data sheet to find variou
bits of information.

What I want to have is some clever formula or vb script in cell A
which will automatically search my data sheet and populate A3 with th
product code of anything in my data sheet with something lik
"stock<100". I then want it to automatically populate down through th
rows: A3, A4, A5, A6 etc.. etc.. finding anything in my data shee
which would match that criteria.

I have attached a current example of how I manually enter the produc
code in column A, and it retrieves the data - but as mentioned above,
want to run a script of some kind which will automtically do this for m
based on a certain criteria.

Can anyone help me please?

Thanks

Attachment filename: example.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=64565

 

[Don Guillett]

Right click on overview sheet tab>view code>copy/paste this>put something
(so it won't be blank) in cell A2 /
Now, if you put 100 in c2 you will populate a3:a? with product codes from
data with <100.
You also need to change your lookup formula to
=IF(ISNA(VLOOKUP(A3,data!$A$3:$C$100,3,0)),"",VLOOKUP(A3,data!$A$3:$C$100,3,
0))
'============
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$2" Then Exit Sub
Application.ScreenUpdating = False
Range("a3:a500").ClearContents
With Sheets("data")
x = .Cells(Rows.Count, "c").End(xlUp).Row
For Each c In .Range("c3:c" & x)
If c < Target Then
y = Cells(Rows.Count, "a").End(xlUp).Row + 1
Cells(y, 1) = c.Offset(, -2)
End If
Next
Application.ScreenUpdating = True
End With
End Sub

If you still don't get it, send me a good email address and I will send you
a workbook.

 

[scoobz]

Excellent! I've got it working now thanks to your help!

Really appreciate it

 

[Don Guillett]

Thanks for the feedback. Glad to help.

 

[scoobz]

Sorry to bug you again.... if you could - there is one more tweak
need to make this perfect:

I need to have an exception:

private sub worksheet_change(byval target as range)
if target.address <> \"$c$2\" then exit sub
application.screenupdating = false
range(\"a3:a500\").clearcontents
with sheets(\"data\")
x = .cells(rows.count, \"c\").end(xlup).row
for each c in .range(\"c3:c\" & x)
if c < target then
y = cells(rows.count, \"a\").end(xlup).row + 1
cells(y, 1) = c.offset(, -2) -Except if value retrieved = 0 (The
ignore and move on to the next one)-
end if
next
application.screenupdating = true
end with
end sub

.....is this also possible

 

[Don Guillett]

try

if c < target then

if c<> 0 and c < target then

 

[scoobz]

Thanks, that did it!

Although on reflection, I realized it was quite a dumb question to as
- and a little lazy of myself not to think a little more.

....thanks again

 

[Don Guillett]

glad to help