We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Course Of The Month
4 days, 16 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
Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Suggested Solutions
| Title | # Comments | Views | Activity |
|---|---|---|---|
| sending mail each sheet to respective DL with a message prompt | 3 | 48 | |
| How to transfer values from one userform in excel to another | 14 | 43 | |
| copy selected cell into new worksheet using checkbox | 15 | 58 | |
| Excel VBA inserting a formula | 12 | 39 |
Introduction
This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results.
Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
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
Course of the Month4 days, 16 hours left to enroll
739 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.