Forum Discussion
#REF error; "not a valid cell/range reference" Help?
I was given a very broken Excel sheet that's supposed to calculate scores for our PT test (Army).
There are errors everywhere. I know what the problem is, I don't know how to fix it. It should be able to take what someone earned on a certain event (deadlift, plank, run, etc.), calculate the score they got for each respective event, and total the scores up. The scores should be determined based on gender and age (per the headers referenced), find their score amongst the correct table, and output the points corresponding to said score.
If I input the scores manually, it tallies it up just fine (picture- third row down: 60x4=300) So the input from the "point score" section is fine. It's the "raw score" section that's not working to find the points earned.
On a separate tab on the same document, there are tables of the score charts separated by event, age, and sex. The score calculator is supposed to be referencing those.
"Function INDIRECT parameter 1 value is 'MDL_F[[#Headers],[17]:[72]]'. It is not a valid cell/range reference."
"MDL_F" refers to the Max Dead Lift event for Females "[17]:[72]" is the cell range of ages for that event.
I am at a loss. Please help.
1 Reply
MDL_F[[#Headers],[17]:[72]] returns some array of texts and INDIRECT doesn't work with arrays. For example
=INDIRECT( {"A1"} ) doesn't work, but
=INDIRECT( "A1" ) works.
Perhaps you may explain in more details what exactly you'd like to do, other ways to achieve result could exists.
