PDA

View Full Version : Formula for a Rent Roll worksheet

ksfrance
2012-01-18, 07:58 PM
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
2012-01-20, 07:56 PM
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
2012-01-23, 07:36 PM
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.

Ken Puls
2012-01-24, 06:50 AM
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
2012-02-09, 10:09 PM
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
2012-02-15, 07:25 AM
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
2012-02-23, 08:33 PM
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!