On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.
Course Of The Month
9 days, 19 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
How do delete or insert values in another cell based on validation selction in adjacent cell
Posted on 2011-03-07
Okay here is where I am at:
My friend and I are trying to creat a NCAA basket ball bracket in excel. I am using validation dropdown lists to popolate the cells and then a second worksheet to create a new validation list for the next round based on what was selected previously. This works great but I want to add the additional functionality that if they select someone in round of 32 then select the same team in the sweet 16 to win but decided that the team would actually lose in the previous round that any rounds after that would clear the name since it was selected to lose prior to that. I am not sure if there is a way through VBA or someother method to make this happen.
I have attached my spreadsheet for reference.
Thanks!
Test-Sheet-3.xlsm
My friend and I are trying to creat a NCAA basket ball bracket in excel. I am using validation dropdown lists to popolate the cells and then a second worksheet to create a new validation list for the next round based on what was selected previously. This works great but I want to add the additional functionality that if they select someone in round of 32 then select the same team in the sweet 16 to win but decided that the team would actually lose in the previous round that any rounds after that would clear the name since it was selected to lose prior to that. I am not sure if there is a way through VBA or someother method to make this happen.
I have attached my spreadsheet for reference.
Thanks!
Test-Sheet-3.xlsm
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
3
2
6 Comments
Hi Apex,
You can use the attached code. BUT you will have to update your two championship games column so they start cleanly in row 6 and not row 7. From the way you designed your file, you shouldn't have any issues doing that.
Thomas
You can use the attached code. BUT you will have to update your two championship games column so they start cleanly in row 6 and not row 7. From the way you designed your file, you shouldn't have any issues doing that.
Thomas
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cl As Range, i As Long, strTeam As String
If Not Intersect(Target, Range("D6:N38")) Is Nothing Then
Application.EnableEvents = False
For i = Target.Column + 1 To 9
strTeam = Target.Offset(, i - Target.Column).MergeArea.Cells(1, 1)
For Each cl In Target.Offset(, i - Target.Column).MergeArea.Cells
If cl.Offset(, -1) = strTeam Then GoTo noIssueLeft
Next
Target.Offset(, i - Target.Column).MergeArea.ClearContents
noIssueLeft:
Next i
For i = Target.Column + 1 To 9 Step -1
strTeam = Target.Offset(, i - Target.Column).MergeArea.Cells(1, 1)
For Each cl In Target.Offset(, i - Target.Column).MergeArea.Cells
If cl.Offset(, 1) = strTeam Then GoTo noIssueLeft
Next
Target.Offset(, i - Target.Column).MergeArea.ClearContents
noIssueRight:
Next i
Application.EnableEvents = True
End If
End Sub
Hi,
<Quoted>
>>if they select someone in round of 32 then select the same team in the sweet 16 to win but decided that the team would actually lose in the previous round that any rounds after that would clear the name since it was selected to lose prior to that. <<
</Unquoted>
Yes , you are totally correct , what do you want the VBA to do for you ?
you stated in yr Validation lists that each cell from [Round of 32] is taking the Value from [Field of 64] and [Sweet 16] is taking from [Field of 64] so on .... then you will never find a previously lost team in the next Fields or round ever :) very logic!
Again , what VBA should do here ?
Thanks
Wellous
<Quoted>
>>if they select someone in round of 32 then select the same team in the sweet 16 to win but decided that the team would actually lose in the previous round that any rounds after that would clear the name since it was selected to lose prior to that. <<
</Unquoted>
Yes , you are totally correct , what do you want the VBA to do for you ?
you stated in yr Validation lists that each cell from [Round of 32] is taking the Value from [Field of 64] and [Sweet 16] is taking from [Field of 64] so on .... then you will never find a previously lost team in the next Fields or round ever :) very logic!
Again , what VBA should do here ?
Thanks
Wellous
nutsch:
I will work with what you gave me today and back with you on the results. It may be tonight before I can run it but I am excited to give it a try and I will make the suggested change to the champion.
Wellous:
I want the list to narrow each time to make sure they only select from teams that are available. However the could select say Arizona in round of 32, and then again in sweet 16 but if they go back and change round of 32 to Utah I want it to delete Arizona from round of 16 since it is no longer a choice. Does that make since?
Thanks again for the responses I will get going on this. After all the tournament is right around the corner!
I will work with what you gave me today and back with you on the results. It may be tonight before I can run it but I am excited to give it a try and I will make the suggested change to the champion.
Wellous:
I want the list to narrow each time to make sure they only select from teams that are available. However the could select say Arizona in round of 32, and then again in sweet 16 but if they go back and change round of 32 to Utah I want it to delete Arizona from round of 16 since it is no longer a choice. Does that make since?
Thanks again for the responses I will get going on this. After all the tournament is right around the corner!
Featured Post
July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Do you use a spreadsheet like Microsoft's Excel? Have you ever wanted to link out to a non excel file on your computer or network drive? This is the way I found to do it!
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month9 days, 19 hours left to enroll
624 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.