I would appreciate some assistance with this issue. This formula is looking up the surname and forename on one sheet and returning a value from another workbook (year 10). I have called the sheets the same name across the two workbooks so that I can use one formula across a multitude of sheets. I would therefore like the "Art" part of the formula to be generic (the current sheet's name but looking for a value on the corresponding one). Does anybody have any ideas to get me started? I've tried Indirect but to no avail...
=IF((VLOOKUP(($A2&" "&$B2),'[Year 10.xlsx]Art'!$C$2:$Z$240,18,FALSE)=0),"",VLOOKUP(($A2&" "&$B2),'[Year 10.xlsx]Art'!$C$2:$Z$240,18,FALSE))
I have tried: =INDIRECT("'"&AJ2&"'!t5") which seems to work but I would like to change t5 so that the formula can be copied and updated to t6, t7 etc.
I then tried: =INDIRECT("'"&$AJ$2&"'!"&AK13) where AK13 contains t5, AK14 has t6 in it which also works. Is there a way of combining the two? I think that would work to a certain extent but I still have to put a pointer to the other workbook on each sheet which isn't ideal...
Frankly, I am not sure what it is you want but with the generic sheet name in A1 and the workbook name in B1 (Year 10.xlsx) maybe this would suit:
=IF(VLOOKUP($A2&" "&$B2,INDIRECT("'["&B1&"]"&A1&"'!$C$2:$Z$240"),18,FALSE)=0,"",VLOOKUP($A2&" "&$B2,INDIRECT("'["&B1&"]"&A1&"'!$C$2:$Z$240"),18,FALSE))