How to use a sheet name in a formula

0
votes

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...

1
excelexcel-formulavlookupworksheet-functionexcel-indirect
Could you give an example of how you used indirect. That is the only method that I can think of that will get you there without VBA. - amoy
@amoy I have updated my question to include my feeble attempts! - Rob W
I'd use VBA if I were you. - Tanner
@pnuts thank you, I have managed to get the current sheet using a similar formula but can't get that into my original one. Any ideas? - Rob W

1 Answers

2
votes

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))