hello guys
I have informations in table "main"
and I want to return a value depends on my choices in another table and cells
let say by chose "RABEE" in cell A2 and "AGE" in cell B2 will appear "28" in the yellow box
that's all
thanks
MacBook Pro 13″, macOS 10.15
Here is a solution, two actually. I think the first of the two is the better solution. You will have to adapt the formulas to your document and tables. If you use a comma for a decimal separator (not the decimal "point"), you will have to change the commas in the formulas to semicolons.
Formulas are:
OFFSET Formula Table cell C2 =OFFSET(Table 1::$A$1,MATCH(A2,Table 1::$A,0)−1,MATCH(B2,Table 1::$1:$1,0)−1)
fill down to complete the table.
INDEX Formula table cell C2 =INDEX(Table 1::A:G,MATCH(A2,Table 1::$A,0),MATCH(B2,Table 1::$1:$1,0))
Fill down to complete the table
The last sentence in the comment in the screenshot means that if you replace Table 1::A:G with Table 1::1:6 in the "index" formula, it will let you add new columns to the data table but not new rows. With Table 1::A:G you can add rows but not columns. The OFFSET formula lets you do both so it is probably the better of the two solutions.
Here is a solution, two actually. I think the first of the two is the better solution. You will have to adapt the formulas to your document and tables. If you use a comma for a decimal separator (not the decimal "point"), you will have to change the commas in the formulas to semicolons.
Formulas are:
OFFSET Formula Table cell C2 =OFFSET(Table 1::$A$1,MATCH(A2,Table 1::$A,0)−1,MATCH(B2,Table 1::$1:$1,0)−1)
fill down to complete the table.
INDEX Formula table cell C2 =INDEX(Table 1::A:G,MATCH(A2,Table 1::$A,0),MATCH(B2,Table 1::$1:$1,0))
Fill down to complete the table
The last sentence in the comment in the screenshot means that if you replace Table 1::A:G with Table 1::1:6 in the "index" formula, it will let you add new columns to the data table but not new rows. With Table 1::A:G you can add rows but not columns. The OFFSET formula lets you do both so it is probably the better of the two solutions.
Or you could use "INDIRECT" like this:
In the example of the INDIRECT function the reference B4 gets "Age" and the reference A4 gets "Rawad".
Do not forget to include the "blank" between the two references B4 and A4.
Paul.
Nice! I've noticed you can also just use:
=INDIRECT("Main::"&B & " " & A)
That way when you turn on "Use header names as addresses" you see:
SG
I noticed this use of INDIRECT is a little finicky, though. There are some issues that I can explain but some inconsistencies I cannot. I was playing around with the table in the screenshot below trying to see what would happen with ambiguous addresses (alpha beta alpha is which cell?). The lower rows show reference.name() of the upper cells. You can also see that most cells are not addressable by their header names.The second screenshot is of the same table after I was messing around with cell A2, changing it to "Alpha 1" or "Alpha1" and back to "Alpha". They should be identical tables but you can see the addressing is different. Why I do not know. It will fix itself if I mess with it more.
As cool and simple as this method is, I think I'll have to stick with INDEX(MATCH()) for most things.
Wow. All these years and I never realized you could use INDIRECT like that. You don't even need to have "use header names as addresses" turned on.
thanks for all
how to return value of cell depends on other cells ?
