Formula for Deleting Text based on Criteria

Does anyone know if there's a way to have text on one sheet automatically removed/deleted based on data/criteria entered on another sheet in the same workbook?

For example, in my attached worksheet I have two sheets, one labeled Values and the other Overall. Both sheets have a list of names, I would like the Overall sheet to have a name and all the data in that row with the name removed/deleted when a value is entered with that same name on the Values sheet.

Let's say I enter a number value in cell E4 of the Values Sheet for the name Mike Trout, I would then want the name Mike Trout and all his information in that corresponding row removed automatically from the Overall sheet. Is this possible?

My worksheet is attached, any help would be greatly appreciated. Thanks!

Originally Posted by davesexcel

Right click on the sheet tab and select "View Code"
Copy and paste the code there.

Please Login or Register  to view this content.

Is this a general code or is this specific to my particular worksheet?

Originally Posted by sktneer

You will place the code on Values Sheet's module and change the Sheet Name in the code if the other sheet is not "Overall".

Please Login or Register  to view this content.

For detail see the attached.
To view the code --> Right Click on Values Sheet Tab--> View Code

Opened up the worksheet, entered a value in cell E4 and the player did not delete from the Overall sheet, any ideas?

Originally Posted by sktneer

Did you enable the macros after opening the file when prompted?

Ya now it works. Only issue I'm having is if I enter a value the player deletes, but if I remove the value the player still stays deleted. Anyway to make the player only be deleted if there is a value entered?

Originally Posted by sktneer

In that case the possible workaround is to hide the row on Overall Sheet rather deleting it. So if you remove the value on the Values Sheet, the row will be visible again.
See if this is something you can work with.

That works but is there anyway to make the popups go away every time you enter a value or delete a value?

Originally Posted by sktneer

Just delete the lines in the code which start with Msgbox "...............". There are two such lines in the code.

Ok that worked thanks. If I wanted to apply this to more than one sheet at a time would I just add more sheets in the code and where?

Originally Posted by mlbdc2012

Ok that worked thanks. If I wanted to apply this to more than one sheet at a time would I just add more sheets in the code and where?

Can anyone help with this?

Originally Posted by sktneer

Your requirement is not clear.
What do you mean by adding more sheets? Will you have one Overall sheet with more than one Value sheets or you will have one Value sheet with more than one Overall Sheets?

One Values Sheet with multiple Overall sheets with the names on each one

Originally Posted by sktneer

Replace the previous Worksheet_Change code with this one.....

Please Login or Register  to view this content.

Does this resolve your issue?

Yes this allows more than one sheet to have the code to apply to, but I would like the ability to choose which sheets to apply it to, I don't want them all to have it just the sheets I pick.

Originally Posted by sktneer

Try this.....

Please Login or Register  to view this content.

Ok so what if I want to use more than one sheet, do i use a comma and then quotations?

Alright thanks!