Put Together a Formula that Looks at 3 Different Cells

[TJVFree]

Hi,

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


TCB

 

[SkipVought]

Hi,

You cannot interactively prevent a user from entering data data based on other values, using native Excel features.

You would need event driven VBA code. Forum707.

 

[combo]

The most you can do without vba is applying data validtion with custom settings. You can build dynamic conditions, but the user is still able to paste incorrect value or clear contents.

combo

 

[zelgar]

Probably you need to have several IF statements in Cells C9, C21 and C90 to see if there's data in Col B for Sections 1, 2 and 3 and put a message in there to create an error. Additionally, instead of have a simple SUM statement in Col B, you also might want to have an IF statement if there's data in the other Sections as well. Finally, I don't understand why you have the data in rows 4-6. I'd only have the Final Score in B2. Also, you're not calculating Percentages for any of the Sections, only the total points. I'd change all of the SUM statements into a SUM()/Total available points (e.g., B18 = SUM(B9:B17)/90) and have B2 =MAX (B18, B25, B33)

 

[TJVFree]

Thank you, I think you are correct about having to do this in VBA

TCB