#This Row reference from Text

[neplecha]

Hi everyone,

I was wondering if it's possible to create #This Row type table reference using a text stored in another cell - I have unsuccessfully tried a few things including INDIRECT but nothing worked.

BACKGROUND: my spreadsheet has a manual input in the cell A1 and a formula stored in a cell A2 that determines which column the A1 value refers to (as I have 12 different columns - it's a complex org structure piece).
So A2 throws column reference (e.g. [Org Unit L3 - ID] based on provided org unit ID) which is fine, however, I'm trying to figure out, how I could use that value to generate 'this row' reference (in this case [@[Org Unit L3 - ID]] ) that would interactively work and change based on A1 & A2.

I can't use macro in this instance, only formulas. Any tips would be really appreciated.
Thank you

 

[StephenCrump]

Welcome to the Forum!

It's not completely clear what your layout is, but here are two ideas that might get you started?

Book10
	A	B	C	D	E	F	G	H
1		MyVals		AA	BB	CC	DD
2	CC	1		6	4	1	2
3		8		4	2	8	8
4		5		9	3	5	2
5		8		1	3	8	10
6
7
8		MyVals	AA	BB	CC	DD
9	CC	1	6	4	1	2
10		8	4	2	8	8
11		5	9	3	5	2
12		8	1	3	8	10
13
Sheet3

Cell Formulas
Range	Formula
B2:B5	B2	=INDEX(Table1,,MATCH(A2,Table1[#Headers],))
B9:B12	B9	=INDIRECT("Table2[@"&A$9&"]")
Dynamic array formulas.

 

[neplecha]

****, it works! I feel stupid. Didn't occur to me try combining the table name with [@... as ordinarily thisrow reference doesn't require the table name. Thank you so much!

 

[StephenCrump]

No problem, glad we could help.

It's easy to overlook the obvious - I've done it many times!