I have a number of worksheets which contain audit score results (they are all the same format), there is one for each individual, therefore I have labelled the worksheets with their names. On a summary worksheet, I want to display the same field in each of the worksheets.
E.g. In Jon's sheet there is:
A B
1 Test Name Test Result
2 Test 1 1
3 Test 2 1
4 Test 3 3
E.g. In Bob's sheet there is:
A B
1 Test Name Test Result
2 Test 1 2
3 Test 2 3
4 Test 3 3
In the summary sheet, I want to see:
A B C
1 Jon Bob
2 Test 1 1 2
3 Test 2 1 3
4 Test 3 3 3
So I have a lookup in cell B2 , which is =VLOOKUP(A2,Jon!$A:$B,2,false). I want to be able to lookup the name so that I don't have to create a separate formula for all the names (I have 70), but I can't find a way to use a formula which the lookup will use as a valid table array.
Please help!
E.g. In Jon's sheet there is:
A B
1 Test Name Test Result
2 Test 1 1
3 Test 2 1
4 Test 3 3
E.g. In Bob's sheet there is:
A B
1 Test Name Test Result
2 Test 1 2
3 Test 2 3
4 Test 3 3
In the summary sheet, I want to see:
A B C
1 Jon Bob
2 Test 1 1 2
3 Test 2 1 3
4 Test 3 3 3
So I have a lookup in cell B2 , which is =VLOOKUP(A2,Jon!$A:$B,2,false). I want to be able to lookup the name so that I don't have to create a separate formula for all the names (I have 70), but I can't find a way to use a formula which the lookup will use as a valid table array.
Please help!