Formula change

[VPLUMWOOD]

Hi,

Ive created a spreadsheet for our Year 6 Teachers to type into to calculate outcomes for children (the green sections - the rest I want to password protects so they cant delete any formulas which is often an issue! ). The problem I have is that during the year they have to do a variety of tests at different points to make sure the children are on track to meet their target.

They use historical papers over a period of 6 years.

At the moment Ive added in a sheet with the raw scores for the 2019 paper but I want to also add in a sheet for 2018, 2017, 2016 etc etc which all have different amounts depending on the average across the country for that particular SATS Year.

I want to some how have a box above assessment point 1 for example that the teacher can type in the SATS Year exam paper they are using at that time and it will auto change the LOOKUP formula from Raw Scores 2019 to the year of the exam paper they are using.

Im sure its doable but beyond my excel knowledge.

Thank you for any help.

 

[Toadstool]

Hi VPlumwood,

You can use INDIRECT to address the correct sheet.

This assumes all year sheets have their lookup values in D4:D112 and results in E4:E112 and that all year sheets follow the naming convention "Raw Scores yyyy".

I have used cell Z1 as the cell to specify the year.

Here are my two sample test data sheets

Here is the "Maths" sheet with the formulae:

VPlumwood.xlsx
	S	T	U	V	W	X	Y	Z
1								2019
2
3	100				61
4	85				13
5	96				45
Maths

Cell Formulas
Range	Formula
S3:S5	S3	=IFERROR(LOOKUP(W3,INDIRECT("'Raw Scores "&$Z$1&"'!$D$4:$D$112"),INDIRECT("'Raw Scores "&$Z$1&"'!$E$4:$E$112")),"Not found")

 

[VPLUMWOOD]

Hi,

Ive done this

=IFERROR(LOOKUP(M3,INDIRECT("'Raw Scores "&$L$1&"'!$A$4:$A$112"),INDIRECT("'Raw Scores "&$L$1&"'!$B$4:$B$112")),"Not found") and the result is showing as not found even though 45 should result in 120?

 

[Toadstool]

Hi,

Ive done this

=IFERROR(LOOKUP(M3,INDIRECT("'Raw Scores "&$L$1&"'!$A$4:$A$112"),INDIRECT("'Raw Scores "&$L$1&"'!$B$4:$B$112")),"Not found") and the result is showing as not found even though 45 should result in 120?

You've added a row so I think you should be using M4 instead of M3.

 

[VPLUMWOOD]

You've added a row so I think you should be using M4 instead of M3.

Yes! Thank you so much!