Need help with multiple conditions and table

[taliesin42421]

Hi all. I am sorry if I am not seeing a similar thread on here anywhere. I am trying to make an excel file that will allow me to score some testing faster. The testing is based on statistical norms within an age group. For example, if an examinee is between the age of 20 and 39 I need to use the norms for age 20-39. I am making worksheets for each age group; that is simple enough. However, the data is on a table where raw score on the left most column needs to match to the raw score on the top most row. I am having difficulty with a formula that looks at the age to switch to the proper worksheet and then matching the data. I have attached an example of the worksheet entitled "Help" As an example, if the raw score of List Learning is "25" and the raw score for Story Memory is "12," I need to have a formula that will find the cell where the two meet, i.e. N28. My goal is to have a separate sheet to put raw scores on that will then use the formula to populate the fields on a more professional looking scoresheet. Any suggestions?

[protonLeah]

Please Login or Register  to view this content.

Replace: Raw Score & Mem Score with cell addresses

[taliesin42421]

Thank you! That works perfectly. There are six different age groups (20-39, 40-49, 50-59, 60-69, 70-79, & 80-89) that have different norms. Would an If/Then statement work to direct the index/match formula based on an age input on the raw score sheet? Thanks again for the help!

[protonLeah]

On each age group sheet, select cells $B$3:$Z$43 and create named ranges corresponding to the sheet, e.g.: _20_39 & _40_49 (notice the underscores).

Since the list/mem score ranges both begin with zero, the INDEX formula can be simplified to
=INDEX('20-39'!B3:Z43,Raw Score+1,Mem Score+1)

To access the different age group sheets, use a data validation dropdown with the sheet names as the source.

Modify the INDEX formula to:

Please Login or Register  to view this content.

On the sample sheet, F2 holds the sheet name, G2 the List Score and H2 the mem score.
20-39 --> INDIRECT("_"&SUBSTITUTE(F2,"-","_")) --> _20_39, i.e., the named range per sheet passed to INDEX.

[taliesin42421]

Thank you; that makes sense. However, there's an order that the assessment is scored and I'm trying to make it as close to the form as the examiners are already used to completing by hand. For some reason I am not able to post an attachment; I've attached screen shotsScore Conversion Page.png. Not all of the data has been input yet, and the formulas on the Supp Discrep Analysis Page have not been put in yet. I was hoping to have the examiners fill in demographics and total scores on the Score Conversion page. The Score sheet and the Supp Discrep Analysis Page are hopefully going to autopopulate. Once the Score Conversion Page is complete, I was hoping to put a macro button on the page that would print the Score sheet and the Supp Discrep Analysis. All of the worksheets excepting the first three are going to be hidden to keep people from messing up the data. With the layout that I have, is there anyway to have the formula search for the appropriate age worksheet based on the input from the Score Conversion Page, cell G4? I'm sure I'm complicating it much more than it needs to be, but I do appreciate your help.

[taliesin42421]

Sorry for the confusion; it was not letting me attach the file...not even under the Advanced options. Attached please find the file with all of the worksheets to give you an idea of what it is that I am hoping to do. Thank you for all of your help.

[protonLeah]

sheet score conversion page V1selects a sheet name:

Please Login or Register  to view this content.

cell D3 returns the conversion value via:

Please Login or Register  to view this content.

D6, D9,D12,D15 will use the same formula, but replacing the List Learning/Story Mem address with Fig Copy/Line Orientation, etc.

[taliesin42421]

Thank you so much! I apologize for the delay in responding. I have been traveling extensively. I greatly appreciate your help!

[taliesin42421]

Thank you again for your help. I had to make a slight change to the formula because the ages were messing up. I added a 30-39 page and edited the formula to:

=CHOOSE(INT(G4/10),"'20-29'!","'20-29'!","'30-39'!","'40-49'!","'50-59'!","'60-69'!","'70-79'!","'80-89'!")


For the final touch I want to add a button that the user can click and it will print two pages, the Score Sheet and the Supp Discrep Analysis Page. I'm getting the Cannot run the macro error and it's driving me quite mad. I've used print button macros before (after xladept on this forum taught me); however, nothing I've tried works. Any thoughts? I tried building the macro from this code; I've even tried using this code that I know has worked before:

Sub PrintSecond()
Dim w2 As Worksheet
Set w2 = Sheets(2)
w2.PrintOut
End Sub

[protonLeah]

Your code prints sheet #2 which happens to be "Score Sheet".
All you need is:

Please Login or Register  to view this content.

or:

Please Login or Register  to view this content.