Return Value based on field result

windsurfit

Board Regular
Joined
Mar 27, 2003
Messages
228
I have a sheet with multiple columns of data.

I would like to have a Subtotal value returned (in cell F3) based on the text entry in a given range - Column J and the corresponding numeric value in Column K

So for example when J9 has the Word "Current" and K9 has the value 100.00 then F3 will result and display $100.00
when J9 has the word 'Prospect" and K9 has the value 100.00 then F3 will result and display $0.00

This is for all data on all rows as my database grows where it finds the "matched" text.


Suggestions??
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
You need to clarify the return value location or condition for returning it. The way it is described, if each populated cell in column J is evaluated for "Current" or "Prospect" and a corresponding value is returned from column K to cell F3, it would only show the last matched value in cell F3. Do you want to use the change event to populate cell F3 as entries are made into column J, or did you want continue on down column F with the values from column K as they are determined? A brief scenario of the objective is always helpful.
 
Last edited:
Upvote 0
Hi, Yes continue on down column F with the values from column K as they are determined.

There will always be corresponding value in Column K for the Column J Text entry.
 
Upvote 0
See if you can use this
Code:
Sub t()
Dim i As Long
With ActiveSheet
    For i = 3 To .Cells(Rows.Count, 10).End(xlUp).Row
        If LCase(.Cells(i, 10).Value) = "current" Then
            .Cells(i, 6) = .Cells(i, 11).Value
        ElseIf LCase(.Cells(i, 10).Value) = "prospect" Then
            .Cells(i, 6) = 0
        End If
    Next
End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top