+ Reply to Thread
Results 1 to 11 of 11

Get result based on true comparison mukltiple tabs of other cells - school report

  1. #1
    Registered User
    Join Date
    11-26-2020
    Location
    Nederland
    MS-Off Ver
    2016
    Posts
    7

    Get result based on true comparison mukltiple tabs of other cells - school report

    Dear experts,

    my problem:
    I have multiple tabs.
    They contain grades of my students.
    I have different tabs for different semesters.
    The first tab contains all grades.

    In the example file;
    Tab 1 = All grades
    Tab 2 = points of a project, grades in column S.

    In tab 2 the names are mixed, because students have worked in groups.

    I want in tab 1 aka "ALL";cell E3. Grades from student name1.
    Based on a comparison of names, because in tab 2 the names are mixed up.

    I don't want to use a VBA.

    Please help me.

    Thank you in advance.
    Attached Files Attached Files
    Last edited by srananmang; 11-27-2020 at 04:39 PM.

  2. #2
    Forum Expert
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    Microsoft 365
    Posts
    1,219

    Re: Get result based on true comparison mukltiple tabs of other cells - school report

    Welcome to the forum.

    Change the Ex. Nr 1 tab name to Exam Nr. 1, so it matches the headings in row 1 of ALL tab. Then use this formula in E3 and copy down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    When new tabs are added (Exam Nr 2, etc), copy across and down.

    Good luck!
    Last edited by Estevaoba; 11-26-2020 at 08:00 PM.

  3. #3
    Registered User
    Join Date
    11-26-2020
    Location
    Nederland
    MS-Off Ver
    2016
    Posts
    7

    Re: Get result based on true comparison mukltiple tabs of other cells - school report

    Thank you for the help.

    It does not work.

    I tried your code just like that, but did not work.
    Tried altering it: =IFERROR(INDEX(INDIRECT("'"&E$1&"'Exam nr. 1'!S3:S12"),MATCH($D3,INDIRECT("'"&E$1&"'Exam nr. 1'!D3:D12"),0)),"")
    Still doesn't give.
    Gives an error with the formula.

    Did you try it out in mine excel file?

  4. #4
    Forum Expert
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    Microsoft 365
    Posts
    1,219

    Re: Get result based on true comparison mukltiple tabs of other cells - school report

    I guess you need to replace commas with semi-colons?
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    It worked for me.
    Please check file.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-26-2020
    Location
    Nederland
    MS-Off Ver
    2016
    Posts
    7

    Re: Get result based on true comparison mukltiple tabs of other cells - school report

    Yes it works now for me.
    Looked Index and Indirect on internet for explenation.
    I have the dutch version so had to replace the (like you said) commas with semi-colons - and translate 'indirect' to 'vergelijken'.

    =INDEX('Exam nr. 1'!$S$3:$S$12;VERGELIJKEN($D3;'Exam nr. 1'!$D$3:$D$12;0);VERGELIJKEN(ALL!$E$1;'Exam nr. 1'!$S$1;0))

    I understood from your first post that I had to change te tab(worksheet) name beneath to be the same as E1.
    But I had to change S1 of Ex.nr1 to the same name of ALL E1.
    It had nothing to do with the tab names, but the column headings.


    I saw your second reply after it worked, but couldn't have done it whitout your first assisst!

  6. #6
    Registered User
    Join Date
    11-26-2020
    Location
    Nederland
    MS-Off Ver
    2016
    Posts
    7

    Re: Get result based on true comparison mukltiple tabs of other cells - school report

    Oooh now i see what you did with the tabs naming.
    I really struggled understanding "'" why the ' withing the "". But it is so the name of the heading of the columns becomes name of tab.

    I think your solution works better for my situation.
    Thank you very much for the help and putting it in the file so I can understand better!

  7. #7
    Forum Expert
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    Microsoft 365
    Posts
    1,219

    Re: Get result based on true comparison mukltiple tabs of other cells - school report

    There’s an old Latin proverb that says:
    Traduttore traditore (translators are traitors)
    And it keeps proving true.
    Glad it worked.
    Have a blessed day.

  8. #8
    Registered User
    Join Date
    11-26-2020
    Location
    Nederland
    MS-Off Ver
    2016
    Posts
    7

    Re: Get result based on true comparison mukltiple tabs of other cells - school report

    new challenge:
    I have one class with two students with the same last name.
    And there are two students with the same first name in the same class.
    The first and last names are in separate columns.

    I have already tried a few things, but now I can't get out of it.
    How do I compare 'ALL'B3:D3 with 'Exam Nr. 1'B3:D12...
    I tried it like this but it does not work.

    Dutch
    Please Login or Register  to view this content.
    English
    Please Login or Register  to view this content.
    It needs to compare each set as one to the name list of the other tab.

    Thank you in forward.
    Last edited by srananmang; 11-27-2020 at 04:37 PM.

  9. #9
    Registered User
    Join Date
    11-26-2020
    Location
    Nederland
    MS-Off Ver
    2016
    Posts
    7

    Re: Get result based on true comparison mukltiple tabs of other cells - school report

    =ALS.FOUT(INDEX(INDIRECT("'"&F$1&"'!S3:S12");VERGELIJKEN($B3:$D3;INDIRECT("'"&F$1&"'!B3:B12"&"'"&F$1&"'!C3:C12"&"'"&F$1&"'!D3:D12");0));"")

    I am thinking like this now, or do I need multiple matches?

  10. #10
    Registered User
    Join Date
    11-26-2020
    Location
    Nederland
    MS-Off Ver
    2016
    Posts
    7

    Re: Get result based on true comparison mukltiple tabs of other cells - school report

    Tried this, with multiple comparisons...
    Please Login or Register  to view this content.
    No luck

  11. #11
    Forum Expert
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    Microsoft 365
    Posts
    1,219

    Re: Get result based on true comparison mukltiple tabs of other cells - school report

    I have made some adjustments so the MATCH function will check both columns B & D.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    But after that tweak, the formula must be array entered. When you finish editing, press Ctrl Shift Enter simultaneously instead of just Enter.
    However, to get around that, I suggest that you add an ID number for each student to make them unique regardless of their names. I have myself met two guys with the same first, second, and third (last) names.
    Please check file.

    Proost!
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 09-07-2018, 02:14 PM
  2. Replies: 4
    Last Post: 11-09-2017, 09:17 PM
  3. 2nd part of this problem: aging report based on date comparison
    By raygon1970 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-02-2015, 01:35 AM
  4. Need help with a date comparison formula - Return text based on result
    By kcleere in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-16-2013, 03:58 PM
  5. IF function based on True result with large formula.
    By naiveprogrammer in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-10-2005, 07:05 AM
  6. [SOLVED] Can Excel operate a function based on a true or false result?
    By SteveD in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  7. school report card template
    By No Principals in forum Excel General
    Replies: 4
    Last Post: 08-19-2005, 10:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1