Finding certain text and then summing annual numbers

[pricepersf]

I work in commercial real estate and use Argus and Excel to underwrite properties. Argus is real estate software that outputs annual/monthly/etc proforma with varying detail depending on the property.

What I'd like to do:
- Use a sheet to annualize the data outputted by a monthly Argus Model. The issue I'm running into is that depending on the property, different expense line items with be within the output and the locations change per output.

I put in reference numbers that auto find the right name/row combination but haven't been able to come up with a formula that works.

Here is where I paste the monthly Argus output. The periods on the top extend for 15 years. The line items on the left change per property.

Here is the sheet where I try to annualize the Argus data. Please disregard the residential section at the top as it doesn't come from Argus. The numbers to the left of the Argus line items are the rows from the previous sheet. I figured I could use these numbers as reference points in a formula. Also, please disregard the data to the right of the line items as I manually linked the data. Sometimes the types of expenses vary per building so I'd like to be able to paste any Argus output into the previous sheet, manually add the line items names in this sheet and have it auto sum for the 15 year timeline on this sheet.

Thanks so much. If you have any followup questions, I'll be here.

-Mark

 

[pricepersf]

Edited to include Mr. Excel's HTML maker

I work in commercial real estate and use Argus and Excel to underwrite properties. Argus is real estate software that outputs annual/monthly/etc proforma with varying detail depending on the property.

What I'd like to do:
- Use a sheet to annualize the data outputted by a monthly Argus Model. The issue I'm running into is that depending on the property, different expense line items with be within the output and the locations change per output.

I put in reference numbers that auto find the right name/row combination but haven't been able to come up with a formula that works.

Here is where I paste the monthly Argus output. The periods on the top extend for 15 years. The line items on the left change per property.Monthly Argus Output

	A	B	C	D	E
1	1		Year 1	Year 1	Year 1
2	2		Month 1	Month 2	Month 3
3	3		Jan-2015	Feb-2015	Mar-2015
4	4		___________	___________	___________
5	5
6	6	Potential Gross Revenue
7	7	Base Rental Revenue	$247,931	$247,933	$247,926
8	8	Absorption & Turnover Vacancy	(153,207)	(153,209)	(153,204)
9	9	Base Rent Abatements	(21,887)	(21,887)	(21,886)
10	10		___________	___________	___________
11	11	Scheduled Base Rental Revenue	72,837	72,837	72,836
12	12
13	13	Expense Reimbursement Revenue
14	14	Real Estate Taxes			164
15	15	Operating Expenses
16	16	Electric	2,189	2,188	2,254
17	17		___________	___________	___________
18	18	Total Reimbursement Revenue	2,189	2,188	2,418

<tbody>
</tbody>

Argus​

Here is the sheet where I try to annualize the Argus data. Please disregard the residential section at the top as it doesn't come from Argus. The numbers to the left of the Argus line items are the rows from the previous sheet. I figured I could use these numbers as reference points in a formula. Also, please disregard the data to the right of the line items as I manually linked the data. Sometimes the types of expenses vary per building so I'd like to be able to paste any Argus output into the previous sheet, manually add the line items names in this sheet and have it auto sum for the 15 year timeline on this sheet.

Annual Profroma

	A	B	C	D	E	F
1	1			Year 1	Year 2	Year 3
2	2		For the Years Ending	12/31/2014	12/31/2015	12/31/2016
3	3
4	4		Gross Residential Income
5	5		General Vacancy	$ -	$ -	$ -
6	6		Get Ready Cost	$ -	$ -	$ -
7	7		Residential Effective Gross Income	$ -	$ -	$ -
8	8
9	9	6	Potential Gross Revenue
10	10	7	Base Rental Revenue	$ -	$ 3,067,050	$ 3,151,183
11	11	8	Absorption & Turnover Vacancy	$ (1,452,379)	$ (405,781)	$ -
12	12	9	Base Rent Abatements	$ (264,592)	$ (272,549)	$ -
13	13
14	14	11	Scheduled Base Rental Revenue	$ 1,274,128	$ 2,388,720	$ 3,151,183
15	15
16	16	13	Expense Reimbursement Revenue
17	17	14	Real Estate Taxes	$ 1,664	$ 7,008	$ 15,835
18	18	15	Operating Expenses	$ 30	$ -	$ -
19	19	16	Electric	$ 47,240	$ 104,087	$ 128,529
20	20	18	Total Reimbursement Revenue	$ 48,880	$ 111,095	$ 144,364

<tbody>
</tbody>

Thanks