Formula for a Rent Roll worksheet

ksfrance

New member
Joined
Jan 18, 2012
Messages
4
Reaction score
0
Points
0
Location
Capistrano Beach, CA
I have a Workbook with many tabs for Lease Properties. I need to add a Worksheet for the current month's Rent Roll. The formula for each property's current month rent needs to refer to that property's worksheet. It would need to extract the value for the rent based on a date range in that same property's worksheet. Can anyone help?
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
I think we'd need to see a sample workbook with the data structure you use, and what your expected outcome would be. You can attach one if you click "Go Advanced".
 

ksfrance

New member
Joined
Jan 18, 2012
Messages
4
Reaction score
0
Points
0
Location
Capistrano Beach, CA
Current Rent Roll

Thank you for your help with this! Attached is a sample mocked up workbook with a Current Rent Roll Worksheet and 3 related Lease Abstract Worksheets. I tried an =IF formula on the first Lease Abstract but it isn't exactly what I am looking for and I would have to manually change the range of cells referenced every month.
 

Attachments

  • TEST Lease Abstracts.xlsx
    43.1 KB · Views: 834

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
I think that you're probably looking for a VLOOKUP statement, but...

Can you explain in a little more detail what you're trying to accomplish and what isn't working? Take the first sheet for example... what cells do you need dynamic, and what do they need to do?

Also, you mention that you'd need to manually change it every month. What changes? The source sheet, or the report?

The more, and clearer, info you can give me, the better I can help you find a solution. :)
 

ksfrance

New member
Joined
Jan 18, 2012
Messages
4
Reaction score
0
Points
0
Location
Capistrano Beach, CA
Hi Ken,
Can you please explain what you mean by dynamic cells? I want the Current Rent Roll sheet to change monthly to reflect the current rent for that month. Each row needs to pull the rent amount from it's corresponding sheet. For example, on the Current Rent Roll sheet Row 7, Bldg 1414, cell E7 should pull the amount of rent for February from the 1414 ACME sheet, cell E36 because we are in the month of February. Is there a way to get the Current Rent Roll sheet to update automatically by changing the dates at the top of the Current Rent Roll sheet and finding the correct rent amount on the corresponding Lease Abstract sheets?
Thanks, Kelly
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Hi Kelly,

What I was after was the following: Which cells will YOU be changing, and which cells need to update automatically. :)

At any rate, I get what you're after now.

In cell E7, enter the following formula: =VLOOKUP($E$3,'1414 ACME'!$C$28:$E$38,3,TRUE)

Unfortunately you can't copy it down directly, as you'll need to update the sheet reference in the formula for each line.

Hope that helps,
 

ksfrance

New member
Joined
Jan 18, 2012
Messages
4
Reaction score
0
Points
0
Location
Capistrano Beach, CA
You're awesome!!!

Thank you so much for the VLOOKUP formula Ken! It worked perfectly and you made me look very good to my client! :) Have a great day!
 
Top