Copying VLOOKUP formula in multiple sheets, where sheet number automatically changes

Question

I have created a progress card in Excel (there are 50 such sheets, one each for 50 students). There is one tabulation sheet also with the names of the 50 students. So, when numeric marks are entered into the tabulation sheet, it automatically converts them to letter grades, which are posted in the individual progress cards, so as a teacher I don't have to make 50 separate progress cards. Now the the problem is I am using the VLOOKUP function, and it's working absolutely fine. But I have to paste it manually into 50 sheets and manually change the sheet number. Is there a different way of doing it?

I'm showing the formula I'm using so it will give a clear picture.

=IF(VLOOKUP(Sheet3!$I$13,'D:\result 2014-15\shcool results\[connect.xlsx]Sheet2'!$B$5:$BL$58,12,FALSE)>27,"A1",
  IF(VLOOKUP(Sheet3!$I$13,'D:\result 2014-15\shcool results\[connect.xlsx]Sheet2'!$B$5:$BL$58,12,FALSE)>25,"A2",
   IF(VLOOKUP(Sheet3!$I$13,'D:\result 2014-15\shcool results\[connect.xlsx]Sheet2'!$B$5:$BL$58,12,FALSE)>22,"B1",
    IF(VLOOKUP(Sheet3!$I$13,'D:\result 2014-15\shcool results\[connect.xlsx]Sheet2'!$B$5:$BL$58,12,FALSE)>19,"B2",
     IF(VLOOKUP(Sheet3!$I$13,'D:\result 2014-15\shcool results\[connect.xlsx]Sheet2'!$B$5:$BL$58,12,FALSE)>16,"C1",
      IF(VLOOKUP(Sheet3!$I$13,'D:\result 2014-15\shcool results\[connect.xlsx]Sheet2'!$B$5:$BL$58,12,FALSE)>13,"C2","D")
 )))))

Answer 1

Your question is unclear.  If you saying that the formula you have shown is the one that you want to enter in Sheet3, and that you need to change the sheet name in each sheet to reference the current sheet, then the first step of the answer is to remove the Sheet3! and just refer to the unqualified $I$13, which will be the one on the current sheet.

Secondly, your formula contains the subexpression

VLOOKUP(Sheet3!$I$13,'D:\result 2014-15\shcool results\[connect.xlsx]Sheet2'!$B$5:$BL$58,12,FALSE)

six times.  It’s common practice, when in such a situation, to put things like this into “helper cells” or “helper columns”.  For example, if you put the above formula (with an = sign) into cell Z1, then your entire formula will collapse to

=IF(Z1>27,"A1",IF(Z1>25,"A2",IF(Z1>22,"B1",IF(Z1>19,"B2",IF(Z1>16,"C1",IF(Z1>13,"C2","D"))))))

But you’re missing out on another simplification, which is to use one more VLOOKUP.  Put the following:

   0   D
13.1   C2
16.1   C1
19.1   B2
22.1   B1
25.1   A2
27.1   A1

somewhere; let’s say Tabulation_Sheet!AA$1:AB$7.  Then your humongous formula reduces to

=VLOOKUP(Z1, Tabulation_Sheet!AA$1:AB$7, 2, TRUE)

… and you can even leave off the , TRUE!