Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

closed as off-topic by Ben N, fixer1234, Steven, nc4pk, mdpc Mar 4 at 0:56

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "This question is not about computer hardware or software, within the scope defined in the help center." – Ben N, fixer1234, Steven, nc4pk, mdpc
If this question can be reworded to fit the rules in the help center, please edit the question.

up vote 0 down vote accepted

Thanks to an answer on the Google support forum, I found the solution:

Thank you, A.P.L.

In GSheets, INDIRECT doesn't support "open-ended" ranges; you will need to specify row numbers:

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

Simple :)

share|improve this answer
    
If you want more explanation, this tutorial does a good job in my experience: spreadsheetpro.net/… – Javaaaa Jul 27 '13 at 18:17

Not the answer you're looking for? Browse other questions tagged or ask your own question.