Numbers: cell references in cell references

Discussion in 'Mac Apps and Mac App Store' started by PCheese, Oct 13, 2009.

  1. PCheese macrumors member

    Joined:
    Aug 9, 2004
    #1
    I'd like to referencing a cell in another table based on other cell values.

    I have three tables: "working", "data A", and "data B". The "working" table has two columns where I enter data manually. For instance, the "working" table might have (with | as a cell delimiter):

    name | level
    ======
    data A | 3
    data B | 2
    data A | 4
    data A | 5

    and the data tables might look like:

    level | value
    ======
    1 | 62
    2 | 95
    3 | 142

    I would like to add a third column to the right which contains the value from the corresponding table + row. For instance, the first row would contain the third cell of the "data A" table, the second would be the second cell of the "data B" table, etc.

    How can I include cell references in a cell reference? Or am I approaching this problem the wrong way?
     
  2. mysterytramp macrumors 65816

    mysterytramp

    Joined:
    Jul 17, 2008
    Location:
    Maryland
    #2
    I'm sure you're doing something simple, but I'm having a hard time visualizing how you want to refer to the separate tables.

    I'd strongly recommend you take a look at the Numbers User's Guide. Chapter 6 talks about formulas and cell references. I bet five minutes and you're on your way.

    mt
     
  3. PCheese thread starter macrumors member

    Joined:
    Aug 9, 2004
    #3
    Thanks, mysterytramp. I'll keep looking through the guide.

    So far, I found a partial workaround using VLOOKUP.

    However, now I have a bunch of ugly nested IF statements. Ideally I'd be able to refer to the table name via a cell value instead of this mess.

    Anyone have a better way of doing this?

    =
    IF($A16="data A",
    VLOOKUP($B16,data A :: $A$2:$D$31,2),
    IF($A16="data B",
    VLOOKUP($B16,data B :: $A$2:$D$31,2),
    0))
     
  4. mysterytramp macrumors 65816

    mysterytramp

    Joined:
    Jul 17, 2008
    Location:
    Maryland
    #4
    Check out the INDIRECT() function.

    Your VLOOKUP can build the range string by pulling "data A" or "data B" (from column A, let's say. Then add the appropriate range identifier (let's say "$A$1:$B$1") then wrap it in the INDIRECT function.

    It'll look something like this:

    Code:
    =VLOOKUP(C3,INDIRECT(A3&"::$A$1:$B$10"),2)
    
    mt
     

Share This Page