Situation:
I have a spreadsheet I use to track tenants and payments, balance due, etc. Each month (12 sheets) a balance due column is figured based on charges and payments for that month plus the balance carryover from the previous month (if any). Each row is by address, but tenants change. When a tenant changes, the balance due formula recognizes the change and does not include the prior month so as to have an accurate balance due by tenant and not property.
What I'm trying to do:
I have a tenant page that lists all tenants throughout the year. I would like to be able to pull in their balance due based on a last name, street number match.
Problem:
I need to create a nested IF/THEN INDEX/MATCH function that will look at the "Move Out" cell on my 'Tenant' sheet to get the sheet reference name, go to the sheet and find the tenant's balance due (based on the last name and street number matches) and return it to the cell.
Example:
Tenant Sheet - Column A is last name, Column C is Street Number, Column J is Balance Due, Column N is Move Out Month (also sheet name)
Monthly Sheets - Column A is Street Number, Column G is Last Name, Column R is Balance Due.
I used the INDIRECT command to bring in the sheet name reference from the cell, but I'm stumped as to how to bring in the INDEX/MATCH portion properly and if I even need to use the IF statements.
Help?
Thanks for reading!!!!
I have a spreadsheet I use to track tenants and payments, balance due, etc. Each month (12 sheets) a balance due column is figured based on charges and payments for that month plus the balance carryover from the previous month (if any). Each row is by address, but tenants change. When a tenant changes, the balance due formula recognizes the change and does not include the prior month so as to have an accurate balance due by tenant and not property.
What I'm trying to do:
I have a tenant page that lists all tenants throughout the year. I would like to be able to pull in their balance due based on a last name, street number match.
Problem:
I need to create a nested IF/THEN INDEX/MATCH function that will look at the "Move Out" cell on my 'Tenant' sheet to get the sheet reference name, go to the sheet and find the tenant's balance due (based on the last name and street number matches) and return it to the cell.
Example:
Tenant Sheet - Column A is last name, Column C is Street Number, Column J is Balance Due, Column N is Move Out Month (also sheet name)
Monthly Sheets - Column A is Street Number, Column G is Last Name, Column R is Balance Due.
I used the INDIRECT command to bring in the sheet name reference from the cell, but I'm stumped as to how to bring in the INDEX/MATCH portion properly and if I even need to use the IF statements.
Help?
Thanks for reading!!!!
