matching scores to names between pagesClosed

Question

I have a situation similar to the original in this thread. 

I'm trying to make a spreadsheet to make score counting easier.

I want all my my registration info on one sheet and the score counting on another. I have column of names on the first sheet that appear as an instance on another where we input their results. The scores are calculated by a simple formula and the results appear in the column next to the instance of their name.

My problem is that since the names on the second sheet are an instance of the first, when the info on the first sheet is changed, then the names and scores on the second sheet no longer match up. 

Is there something I can do that will make the numbers on the second sheet follow the order of the names when they are sorted on the first page?

venkat1926 · Answer

you use vlooklup in the second sheet referring the names in the first sheet. whenever data for the names in the first sheet changes the data for names in second sheet also will change. There is no need for the two sheets in the same sorting order. 

a typical vlookup formula in the second sheet will be like this 
This will be in B1.(you can copy B2 down)
=VLOOKUP(A1,Sheet1!$A$1:$B$4,2,0)

rizvisa1 · Answer

I think your formula approach ( sheet1!A2 ) for sheet 2 will not work. Well ok it can work but I think it would be tedious.  

My suggestion would be use macro to put the value in sheet 2 instead of formula

Presuming
1. All names on sheet 1 would appear in sheet 2
2, It is ok to have name appear on same cell of either sheets (sheet 2 Col A is carbon copy of sheet 1 col A)
3. Any new name would be added  in the new row at the end of existing data
4. you are not going to insert a new row (in the middle of data)

In that case
 you can apply this macro on SHEET1


Private Sub Worksheet_Change(ByVal Target As Range)
Dim tRow As Long
Dim tCol As Integer

    Select Case Target.Columns.Count
    
        Case ActiveSheet.Columns.Count
        
            'if all columns are select then presuming that that row is being deleted
            Sheets("Sheet2").Range(Target.Address).Delete
            Exit Sub
        
        Case Is = 1
        
            ' if the column being changed is not A, then exit
            If Target.Column <> 1 Then Exit Sub
        
        Case Is > 1
            MsgBox ("Unhandled condition")
            Exit Sub
    
    End Select

    
    ' repeat action done on sheet 1 on sheet 2
    For Each cell In Target.Cells
        Sheets("Sheet2").Range(cell.Address) = Sheets("Sheet1").Range(cell.Address)
    Next
            
    
    
End Sub

Basic idea is that if you make any change in column A (change spelling, add a new row at the end or delete a row), same action is replicated on sheet two. Now if you sort sheet 1, you just have to sort sheet 2 too. In a sheet two data is not dependent on sheet 1 (unless you change data on sheet 1)

I have not tested it, thoroughly so there might be a glitch here and there,. but I am sure you get the idea.

UlethClimbing · Answer

I get where you're coming from, and that looks like my best option if I can't make this work the way I'd like too, but I have a lot more going on in this speadsheet than just the two sheets. 

I'm making this spreadsheet as a template for a University climbing club event where names will be inputed, sorted, and resorted with additional names being inputed thoughout the day, and I'm trying to make something that will sort automatically and keep the scores with the right names regardless of how many changes are made. 

The challenge is to try and leave the club with something that is essntially foolproof, so that anyone could use it.

UlethClimbing · Answer

P.S. The code you gave me works perfect, so if I can't figure how how to do anything else, this is still a lot better than before.

rizvisa1 · Answer

May be if you could share you workbook, some thing might jump out. 

Other option could be have a sort of form where data is entered and when you press a button, the data goes where ever it needs to go. This can also allow for that auto sort that you are thinking about.  

Is there any thing like foolproof ?  Just reminded of a part of my life when I used to challenge my boss, break my code. And invariably Aurelia would break it on her first try.

venkat1926 · Answer

you should not put the formula like =sheet1!A2 in sheet 2
what you should do highlight column A and copy and paste it in RangeA1 of sheet2 BEFORE sorting sheet1

UlethClimbing · Answer

Sure I can share my work book, you can find a copy here: 

people.uleth.ca/~rock.climbing/files/download.html 

There's nothing fancy with the code, just one formula and a lot of copy and paste, but you can see it's obvious weaknesses. 

Ultimately I want to come up with something that'll automatically sort the results the way they look on our blog. 

And as far as foolproof code, these kids are rock climbers with no greater aspirations in life than to get a career that lets them play outdoors everyday. Any form of code would be unbreakable to them. 

Thanks for your help

rizvisa1 · Answer

Looking all the formulas spread across the sheets, I am convinced that the best option is not allow the user to enter name on the main screen. But rather have it entered in some other sheet or form and have them click a button which will populate the result. It will make it "dart proof" as nothing is "bullet proof"

UlethClimbing · Answer

That would be amazing, I'm excited to see what you come up with.

Matching scores to names between pages

9 responses

Similar discussions

Subscribe To Our Newsletter!