Put Together a Formula that Looks at 3 Different Cells

[TJVFree]

I’m trying to put together a formula that looks at 3 different cells, and returns the cell that has data in it, and block from putting data in the other selection.

IF ANY INFORMATION IS COMBINED IN CELL B2,B7,B9. an error will show
If B18 = 90 PUT 100% IN CELL b1, IF B25 = 40 PUT 100% IN CELL b1, IF B33 = 20 PUT 100% IN CELL b1, 0

Please see the attachment for more detailed information, please also look at both tabs to help understand

Thank you for your time and help

http://files.engineering.com/getfil...c99c-4e8a-9d48-9e9a13cde35e&file=SCORING.xlsx

TCB

 

[SkipVought]

hi,

Activate your sheet.

Right-click on the Sheet Tab and select View Code

Paste this procedure into your code module.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, [rSection1]) Is Nothing Then
        If Application.CountA([rSection2]) + Application.CountA([rSection3]) > 0 Then
            Application.EnableEvents = False
            Target.ClearContents
            Application.EnableEvents = True
        End If
    End If

    If Not Intersect(Target, [rSection2]) Is Nothing Then
        If Application.CountA([rSection1]) + Application.CountA([rSection3]) > 0 Then
            Application.EnableEvents = False
            Target.ClearContents
            Application.EnableEvents = True
        End If
    End If

    If Not Intersect(Target, [rSection3]) Is Nothing Then
        If Application.CountA([rSection1]) + Application.CountA([rSection2]) > 0 Then
            Application.EnableEvents = False
            Target.ClearContents
            Application.EnableEvents = True
        End If
    End If
End Sub

 

[SkipVought]

Oops, sorry. One other essential ONE TIME task.

Susessively, select each of the ranges in each section that your formula sums and Name each range according to the section name...

rSection1, rSection2 & rSection3 respectively.

 

[SkipVought]

Oops, sorry again.

Successively....

 

[TJVFree]

Not working - see attached

TCB

 

[SkipVought]

What you did do is overwrite your formula with some meaningless text, copied from my post!???

Have you never used Named Ranges?

Select B9:B17 and use the Name Box to name this range rSection1.

Repeat for each successive section.

 

[SkipVought]

...and you never pasted the VBA code into the code sheet for the sheet!

 

[SkipVought]

BTW, when you SAVE a .xlsx workbook that contains VBA macros, you get a message that wans you that if you want to keep your code, you need to save the workbook as a MACRO ENABLED WORKBOOK!.

 

[TJVFree]

its still not working

TCB

 

[SkipVought]

You have totally not read my posts!

1) the Named Ranges ALL refer to incorrect ranges!!!

2) you have not saved the workbook as a macro enabled workbook! Consequently, there is no code!

 

[TJVFree]

Rocking Job SkipVought, works perfect

TCB

 

[Andrzejek]

To say: “Thank you” use: [blue]Great post? Star it![/blue] on helpful post(s)

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.