Formula for looking up data on another sheet

[NWPhotoExplorer]

Hello all,

I built a formula to generate text in a field (J11) that would tell me where to look to get the data off a different sheet (JOB COST) that I need. But now I can't get that text to convert and actually look up the data in that cell (JOB COST!M20).

I have a drop down where I can select my version (B6) and from there it is tells me what column and row I need to look up to get the quantity. This sheet is used to keep track of the revisions and what line items I am bill each time we have to revise the design. But I can't get the text to pull the data from the other sheet. It is just displaying the formula as text. What do I need to do to make this work and actually look up the value and display that? I have been messing with this all day and I cannot find a solution on the internet. I'm hoping this is something simple that I have overlooked.

Thanks!

ESTIMATE ADJUSTMENT - new p2 rates.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1										Original Design	V02	V03	V04	V05	V06	V07	V08	V09
2										1	3	2	6	4	5	8	7	9
3
4
5		Select Version		1	M					1	2	3	4	5	6	7	8	9
6		Original Design								M	N	O	P	Q	R	S	T	U
7
8
9
10		Item	QTY	QTY Reserved	Needs to be Reserved
11		D1	'JOB COST'!$M$20		#VALUE!					'JOB COST'!$M$20
12		D2	2	1	1.00
13		D3	3		3.00
14		D4	4	1	3.00
15		D5	5		5.00
16		D6	6		6.00
Reserved Qty

Cell Formulas
Range	Formula
J2:R2	J2	=IFERROR(MATCH(J$1,'JOB COST'!$M$15:$U$15,0),"")
D5	D5	=HLOOKUP(B6,J1:S2,2,FALSE)
E5	E5	=HLOOKUP(D5,J5:S6,2,FALSE)
J11	J11	=CONCATENATE("'JOB COST'!$",HLOOKUP(D5,J5:S6,2,FALSE),"$20")
C11	C11	=J11
C12	C12	=IF($B$6="Original Design",'JOB COST'!$M$21,"")
C13	C13	=IF($B$6="Original Design",'JOB COST'!$M$22,"")
C14	C14	=IF($B$6="Original Design",'JOB COST'!$M$56,"")
C15	C15	=IF($B$6="Original Design",'JOB COST'!$M$57,"")
C16	C16	=IF($B$6="Original Design",'JOB COST'!$M$58,"")
E11:E16	E11	=IF((C11-D11)>0,C11-D11,"")

Cells with Data Validation
Cell	Allow	Criteria
B6	List	Original Design, V02, V03, V04, V05, V06, V07, V08, V09, V10

 

[Peter_SSs]

Try this in J11. It is just your formula wrapped in an INDIRECT function

=INDIRECT(CONCATENATE("'JOB COST'!$",HLOOKUP(D5,J5:S6,2,FALSE),"$20"))