Formula change

VPLUMWOOD

New Member
Joined
Jul 13, 2021
Messages
11
Office Version
  1. 365
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! :ROFLMAO:). 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.



Picture1.jpg
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,002
Office Version
  1. 2016
Platform
  1. Windows
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

1626265492957.png



Here is the "Maths" sheet with the formulae:

VPlumwood.xlsx
STUVWXYZ
12019
2
310061
48513
59645
Maths
Cell Formulas
RangeFormula
S3:S5S3=IFERROR(LOOKUP(W3,INDIRECT("'Raw Scores "&$Z$1&"'!$D$4:$D$112"),INDIRECT("'Raw Scores "&$Z$1&"'!$E$4:$E$112")),"Not found")
 

VPLUMWOOD

New Member
Joined
Jul 13, 2021
Messages
11
Office Version
  1. 365
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?
 

Attachments

  • Picture3.jpg
    Picture3.jpg
    217.5 KB · Views: 2

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,002
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,144,163
Messages
5,722,853
Members
422,461
Latest member
kelleys315

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top