VLookup across several sheets

[jabeck]

example:
I have sheet named "Dept. 1", "Dept. 2" and "Dept. 3" each containing a list with 2 columns as follows.

dept. 1 Dept. 2 Dept. 3
1 account Value account Value account Value
2 120 100 100 1200 110 1000
3 130 120 120 100 120 1100
4 140 1100 150 110 140 120

Now i want to create a sheet "total" that looks like this.
1 Account dept. 1 dept. 2 dept. 3
2 100 N/A 1200 N/A
3 110 N/A N/A 1000
4 120 100 100 1100
5 130 120 N/A N/A
6 140 1100 N/A 120
7 150 N/A 110 N/A

I had the idea to make the following formula in B2 and copy it
=VLOOKUP($A2;"'"&$B1&"'!$A$2:$B$4;2;FALSE)
But it doesn't do the trick.

Any help would be apreciated.

 

[Sumo1813]

What is your lookup value?
I like to put my data in order from left to right, so that I can just change the column index. Can you give more information on your data, it is a little confusing to me.

 

[jabeck]

It seems all my formating was removed in the process.
I have nov tried to make the columns more readable
First line is sheetName, second line Column and first number on the rest of the lines are rownumbers

Sheet dept. 1
.....A.....B
1 acct Value
2 120 100
3 130 120
4 140 1100

Sheet dept. 2
.....A.....B
1 acct Value
2 100 1200
3 120 100
4 150 110

Sheet dept. 3
.....A.....B
1 acct Value
2 110 1000
3 120 1100
4 140 120


Now i want to create a sheet "total" that looks like this.
Total
.....A.................B.................C..................D
1 Account ........dept. 1 ......dept. 2 .........dept. 3
2 100 ...............N/A ...........1200 ............N/A
3 110 ...............N/A .............N/A ...........1000
4 120 ...............100 .............100 ...........1100
5 130 ...............120 .............N/A ............N/A
6 140 ..............1100 .............N/A ............120
7 150 ................N/A .............110 ............N/A

I had the idea to make the following formula in B2 and copy it
=VLOOKUP($A2;"'"&$B1&"'!$A$2:$B$4;2;FALSE)
But it doesn't do the trick.

 

[Sumo1813]

It should be just a matter of writing three separate Vlookups and then pasting them down...(vlookup($a2,sheetdepot.1,$A$2:$B$4,2,false)...then the only thing you would have to change is the number of the sheet.

 

[pgc01]

Hi jabeck

"'"&$B1&"'!$A$2:$B$4

gives you a string where VLookup() expects a reference.

You can use Indirect() to transform a string into a reference. Try:

=VLOOKUP($A2;INDIRECT("'"&$B1&"'!$A$2:$B$4");2;FALSE)

 

[pgc01]

P. S.

Also for the worksheet name you want to freeze the row not the column

Try:

=VLOOKUP($A2;INDIRECT("'"&B$1&"'!$A$2:$B$4");2;FALSE)

 

[jabeck]

answer # 5
I now i can do that, but this was an example, in real life there are 80 sheets and i have to lookup 12 column in each. Therefore i am looking for a "smarter" way.

 

[jabeck]

ray: pgc01 "My hero",
thanks a lot, you saved my day. This was exactly what was needed.