I'm trying to convert an Excel spreadsheet into Google, but can't get this VLookup(Indirect)) combination to work.

My data sheets are named with a code, for example "fred01", and look like this:

      ---A--- ----B----
1]    FIELD   VALUE
2]    Name    Fred
3]    Date    18/01/2010
4]    Amount  1234
5]    Status  active


Column A is always field-names, column B is always the values.

My summary sheet needs to get the values from the various sheets, not all of which have all the fields. New fields are added, as are new sheets, all the time.

So, in Excel, I would simply do:

     ---A--- ---B--- ---C---
1]   CODE    AMOUNT  FORMULA
2]   fred01   1234   =VLOOKUP("Amount",INDIRECT(CONCATENATE(A2,"!$A:$B")), 2, False)
3]   jane01    144   =VLOOKUP("Amount",INDIRECT(CONCATENATE(A3,"!$A:$B")), 2, False)
4]   sam01      82   =VLOOKUP("Amount",INDIRECT(CONCATENATE(A4,"!$A:$B")), 2, False)


=VLOOKUP("Amount",INDIRECT(CONCATENATE(A2,"!$A1:$B99")), 2, False)