Forum Discussion
Can't close the formula
How do I close this formula? It involves checking data and searching for it on multiple sheets. My colleague gave me this info:
For the Lookups from the form to the year data, I would recommend inserting a new column on the far left and add =CLASSCELL&NAMECELL (e.g. =B4&C4)
This would need to be done for the individual form sheets and the year data, so you have a common cell to lookup.
Then use an if statement to check the columns A match, then a VLOOKUP to pull the data through. To test this, I did 'define name' tables in 3 class sheets for 8S, 8P and 8T (_8S, _8P and _8T).
All sheets would need to have a defined name.
To lookup the assessment objectives - average A for the first student, I used this formula:
=IF(A4='8T'!A4,VLOOKUP(A4,_8T,4,FALSE),IF(A4='8S'!A4,VLOOKUP(A4,_8S,4,FALSE),IF(A4='8P'!A4,VLOOKUP(A4,_8P,4,FALSE),"error")))
But when I try that formula, it says it is missing something and won't work. Please can you tell me what it is missing?
This is the formula:
=IF(A4='8P'!A4,VLOOKUP(A4,CLASS8P,4,FALSE),=IF(A4='8O'!A4,VLOOKUP(A4,CLASS8O,4,FALSE),=IF(A4='8S'!A4,VLOOKUP(A4,CLASS8S,4,FALSE),=IF(A4='8H'!A4,VLOOKUP(A4,CLASS8H,4,FALSE),=IF(A4='8t'!A4,VLOOKUP(A4,CLASS8T,4,FALSE),"ERROR"))))
=IF(A4='8P'!A4,VLOOKUP(A4,CLASS8P,4,FALSE),IF(A4='8O'!A4,VLOOKUP(A4,CLASS8O,4,FALSE),IF(A4='8S'!A4,VLOOKUP(A4,CLASS8S,4,FALSE),IF(A4='8H'!A4,VLOOKUP(A4,CLASS8H,4,FALSE),IF(A4='8t'!A4,VLOOKUP(A4,CLASS8T,4,FALSE),"ERROR")))))
This formula works in my sheet.
