Using Indirect to Pull information from another tab

[shyy]

Hi everyone,

I am in need of help with the INDIRECT function, what I am trying to do is return values from different tabs. The name of the tab is in ROW 8. The value I am trying to return is the same line Item that is in ROW 10. There are basically two rows I need to search (they are the total for that line item). First without the SP is in ROW 23 and with SP is in ROW 47.

So if I need line item 2, it will look at the tab name from ROW 8 of the tax allocation tab and do a match and return where the column number is ROW 6 from the underlying k1 tab. After the column number is return to add in the 23 so it will always pick up ROW 23 (D23) and same if it is SP ROW 47 (D47). In this case NON SP would return the amount -52,792 and the SP 203,774 in the allocation tab I highlighted in red fonts.

Thanks in advnce

Excel 2010

	B	E	I	J	K	L	M	N	O	P
5
6
7				SP					SP
8		Tabs	underlying k-1s	underlying k-1s			RE(2011 WP)	underlying k-1s	underlying k-1s
9
10		Line Items	Line 1	Line 1				Line 2	Line 2
11		CAPITAL	Ord. Income	Ord. Income	Ord. Income		Rental Income	Rental Income	Rental Income	Total
12		1/1/2012
13	NAME	97,945,902	(52,792)	223,704	170,912		321,037	(6,085)	30,020	344,972
14
15
16	Partner 1	1,461,584	-	-	-		-	-	-	-
17	Partner 2	33,521,792	(18,739)	-	(18,739)		113,957	(2,160)	-	111,797
18	Partner 3	14,485,576	(8,095)	-	(8,095)		49,235	(933)	-	48,302
19	Partner 4	17,151,413	(8,652)	201,334	192,682		52,615	(997)	27,018	78,636
20	Partner 5	15,703,384	(8,652)	11,185	2,533		52,615	(997)	1,501	53,119
21	Partner 6	15,622,153	(8,652)	11,185	2,533		52,615	(997)	1,501	53,119
22
23		97,945,902	(52,790)	223,704	170,914		321,037	(6,084)	30,020	344,973

<TBODY>
</TBODY>

Tax Allocation(2011 WP)​

Excel 2010

	B	C	D	E	F	G	H	I	J
1
2	2012 K-1 Summary
3
4
5		Name				Income
6		Underlying K-1:	Line 1				Line 2		Line 3
7			Ordinary business inc (loss)	Ordinary deduction		Total	Net rental real estate income	Total	Other Rental Income
8	1	Passthrough 1				-		-
9	2	Passthrough 2				-		-
10	3	Passthrough 3				-		-
22
23		Total	(52,792)	-	-	(52,792)	(6,085)	(6,085)	(463)
24
25
26
30
31
32
33	13	Special Project 1	(9)			(9)	(1,332)	(1,332)
34	14	Special Project 2				-	(36,334)	(36,334)
35	15	Special Project 3	90,964			90,964		-
47		Total	223,704	-	-	223,704	30,020	30,020	-

<TBODY>
</TBODY>

underlying k-1s​

 

[Andrew Poulsom]

Like this?

=INDEX(INDIRECT("'"&D8&"'!B1:J47"),23+(24*(D7="SP")),MATCH(D10,INDIRECT("'"&D8&"'!B6:J6"),FALSE))

 

[shyy]

Thanks Andrew,

I came up with this

=IF(C3="SP",INDIRECT("'"&C4&"'!"&ADDRESS(21,MATCH(C6,INDIRECT("'"&C4&"'!$A$5:$I$5")))),INDIRECT("'"&C4&"'!"&ADDRESS(11,MATCH(C6,INDIRECT("'"&C4&"'!$A$5:$I$5"))

I will dissect your formula, appreciate it!

Can you explain this part 24*(C7="SP")

 

[Andrew Poulsom]

I just noticed your hidden columns. In I13 the formula would be:

=INDEX(INDIRECT("'"&I8&"'!B1:J47"),23+(24*(I7="SP")),MATCH(I10,INDIRECT("'"&I8&"'!B6:J6"),FALSE))

 

[shyy]

Unfortunately I forgot to save the file and lost it due to a crash. I created another one and it seems to only work for NON SP. I would have to manually adjust the ROW number from 8 or 18.

Excel 2010

	A	B	C	D	E	F	G	H	I	J
1				SP					SP
2		Tabs	Underlying K1	Underlying K1			RE(2011 WP)	Underlying K1	Underlying K1
3
4		Line Items	Line 1	Line 1				Line 2	Line 2
5		CAPITAL	Ord. Income	Ord. Income	Ord. Income		Rental Income	Rental Income	Rental Income	Total
6		1/1/2012
7	NAME	97,945,902	-52,792	223,704	170,912		321,037	-6,085	30,020	344,972
8			.
9
10	Partner 1	1,461,584	-	-	-		-	-	-	-
11	Partner 2	33,521,792	-18,739	-	-18,739		113,957	-2,160	-	111,797
12	Partner 3	14,485,576	-8,095	-	-8,095		49,235	-933	-	48,302
13	Partner 4	17,151,413	-8,652	201,334	192,682		52,615	-997	27,018	78,636
14	Partner 5	15,703,384	-8,652	11,185	2,533		52,615	-997	1,501	53,119
15	Partner 6	15,622,153	-8,652	11,185	2,533		52,615	-997	1,501	53,119
16
17		97,945,902	-52,790	223,704	170,914		321,037	-6,084	30,020	344,973

<TBODY>
</TBODY>

tax allocation​

Worksheet Formulas Cell Formula C7 =INDEX(INDIRECT("'"&C2&"'!B1:J100"),8+(24*(C1="SP")),MATCH(C4,INDIRECT("'"&C2&"'!B2:J2"),FALSE)) D7 =INDEX(INDIRECT("'"&C2&"'!B1:J100"),18+(24*(C1="SP")),MATCH(C4,INDIRECT("'"&C2&"'!B2:J2"),FALSE)) H7 =INDEX(INDIRECT("'"&H2&"'!B1:J100"),8+(24*(H1="SP")),MATCH(H4,INDIRECT("'"&H2&"'!B2:J2"),FALSE)) I7 =INDEX(INDIRECT("'"&H2&"'!B1:J100"),18+(24*(H1="SP")),MATCH(H4,INDIRECT("'"&H2&"'!B2:J2"),FALSE)) <TBODY> </TBODY>

<TBODY>
</TBODY>

Excel 2010

	A	B	C	D	E	F	G	H	I
1		Name				Income
2		Underlying K-1:	Line 1				Line 2		Line 3
3			Ordinary business inc (loss)	Ordinary deduction		Total	Net rental real estate income	Total	Other Rental Income
4	1	Passthrough 1				-		-
5	2	Passthrough 2				-		-
6	3	Passthrough 3				-		-
7
8		Total	-52,792	-	-	-52,792	-6,085	-6,085	-463
9
10
11
12
13
14
15	13	Special Project 1	-9			-9	-1,332	-1,332
16	14	Special Project 2				-	-36,334	-36,334
17	15	Special Project 3	90,964			90,964		-
18		Total	223,704	-	-	223,704	30,020	30,020	-

<COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5"><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Underlying K1​

 

[Andrew Poulsom]

Isn't it?

=INDEX(INDIRECT("'"&C2&"'!B1:J100"),8+(10*(C1="SP")),MATCH(C4,INDIRECT("'"&C2&"'!B2:J2"),FALSE))

 

[shyy]

Thanks again, I now understand where you got the 10 from.