0
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)
In Google Spreadsheets however, this returns an #N/A error.
If you want more explanation, this tutorial does a good job in my experience: http://spreadsheetpro.net/how-to-make-a-dynamic-reference-to-a-worksheet-in-excel-and-google-spreadsheets/
– Javaaaa – 2013-07-27T18:17:26.727