Results 1 to 7 of 7

Thread: Formula for a Rent Roll worksheet

  1. #1
    Neophyte ksfrance's Avatar
    Join Date
    Jan 2012
    Location
    Capistrano Beach, CA
    Posts
    4
    Articles
    0

    Formula for a Rent Roll worksheet



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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?

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    1,722
    Articles
    76
    Blog Entries
    14
    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".
    Ken Puls, CMA, MS MVP (Excel)

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Neophyte ksfrance's Avatar
    Join Date
    Jan 2012
    Location
    Capistrano Beach, CA
    Posts
    4
    Articles
    0

    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.
    Attached Files Attached Files

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    1,722
    Articles
    76
    Blog Entries
    14
    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.
    Ken Puls, CMA, MS MVP (Excel)

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Neophyte ksfrance's Avatar
    Join Date
    Jan 2012
    Location
    Capistrano Beach, CA
    Posts
    4
    Articles
    0
    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

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    1,722
    Articles
    76
    Blog Entries
    14
    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,
    Ken Puls, CMA, MS MVP (Excel)

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    Neophyte ksfrance's Avatar
    Join Date
    Jan 2012
    Location
    Capistrano Beach, CA
    Posts
    4
    Articles
    0

    You're awesome!!!



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •