[Closed] Excel Question

here is what I'd do, on a new / hidden sheet do a nested vlookup this =if(vlookup(a1,a2:b6,2)>0,0,(vlookup(a1,a2:b6,2)), that will pull all the negatives

then sum the whole range all in a seperate cell

"only show the negative ones all in one cell"

explain?

As a list? "-4, -5, -24, -54"
or a sum? "-87"

or what?

Sounds like you might need to write a custom vba function to read in the range and loop through each value in the range, append each negative value found into a string variable and return that variable.

something like

Function Negatives (ParamArray arglist() as Variant) as String

Dim cell As Range

Negatives = ""

For Each arg In arglist
For Each cell In arg
If cell < 0 Then Negatives = Negatives & "," & cell
Next Cell
Next arg

End Function

In your spreadsheet use this function like any other:

=Negatives(A1:A100)

Should also work with multiple ranges : eg =Negatives(A1:A100,B1:B50)

Just tested it and it works fine. I can send it to you if you like?

I think I've done it..

If vlookup.....<0,vlookup.......,0

Cheers
Steve

Cleaner version:

Function Negatives(ParamArray arglist() As Variant) As String

Dim arg As Variant

Dim cell As Range

Negatives = ""

For Each arg In arglist
For Each cell In arg
If cell < 0 Then
If Negatives = "" Then
Negatives = cell
Else
Negatives = Negatives & "," & cell
End If
End If

Next cell
Next arg

End Function

exctly what I was thinking steve-g. No need to get into vb unless completely necessary in my opinion!