How to make rows autofill from other cells when data is entered and ignore empty cells

What my my boss wants is for rows to auto fill when a tenant(s) pay rent on any given day and then to ignore days when no one pays.

For example: (All on the Ledger2 Tab)

Rows B33:AF57 is where the data will come from

What the boss wants is for an end result like in rows A62:A68 (Done Manually)

As you can see, Tenants paid rent on the 1st, not on the 2nd, then on the 3rd, 4th, 5th and 6th but these could be any tenant or tenants on any days.

He does not want a row dedicated to any day he only wants them to autofill when paid

Any help would be appreciated

Shawn

jaslake,

Just like the example in rows 62-68 it would need to show the date, tenant and the amount for all tenants that paid on that day. It can be on a separate tab from the source information however, it needs to be on the same tab as the 1st 30 rows as it is part of a ledger that gets printed for the board

Thank you so much for your help

Shawn

=aggregate(15,7,(row($a$1:$ae$15)+10000*column($a$1:$ae$15))/($b$33:$af$57>0),rows($a$62:$a62))

Thank you for your response, I was however unable to make it work

Shawn

I am not sure what happened previously, but i just tried it and you are correct! Thank you very much for your help

Shawn

jaslake,

Again, Thanks

I would ask, however; is it possible to rename the tab from "Temp" to something else? I have tried and was unsuccessful.

Oh, and were the Tabs labeled 8-31 removed? If so,no problem, I will just recreate them.

Shawm

Jaslake,

Ledger is what it should be called.

Also, on each tab (1-31) there is a column where they put the tenants names and then the next column is the amount of rent paid. I have inserted a Activex Combobox, i really dont want to copy AND align that combox box 11 times for each of the 31 sheets, is it possible to copy it once and then paste it where I want it?

Jaslake,

I was using that ledger tab as the basis for what they have so that I had the original to refer back to. In the final version the current "Ledger and Ledger2 Tabs will be deleted.

Im afraid I do not understand about the TempCombo Code your referring to, should I just copy that to all tabs?..........

Shawn

jaslake,

OK, Ive attached the completed DCIR.

I have included an "Example" tab that shows what it should look like (including page breaks so it prints properly).

The "Example" tab is to be removed.

The "Reference" tab is basically the same as the old "Ledger 2" tab. I would like it to be hidden (but completely unnecessary).

I would like the new tab that will pop up ("TEMP" in your old version) be named Ledger

Thanks again

Shawn

I hope this is what you wanted. I have randomly populated days throughout the month. Any tenant could conceivably pay on any day of the month so it needs to work throughout the month.

Shawn

P.S. Sorry about your knickers

Sorry, did not see that you needed "Example populated, here you go

Shawn

M&O is manually entered. Im not sure why they do it this way, but they subtract M&O from amount paid ("Amount") then re-add it in ("Total") with formulas.

I did not try put exactly the same tenants and amounts In "Example" as I did on the daily tabs as I was just trying to show it looks. The bottom line is that they reflect what is shown on the daily tabs or the "Reference" tab (wherever your referencing in your code). I thought that you were referencing the "Reference" tab in your code which is a direct correlation to the daily tabs, I only filled it for visual reference.

In the end, it needs to look like "Example" tab with the difference being that what days what tenants pay what amounts will differ (They could all pay on the 1st, or some on the 1st, some on the 3rd some on the 10th, etc.)

I have done as you asked for days 1,3,5,7 and 9

As I do not know how you do this since my knowledge is minuscule by comparison, please do not take offense when I reiterate that what tenant will pay what amount on any given day throughout the month varies so I need the code to work for all tenants on all 31 days.

As always I hope this finds you well and again thanks for your help

Shawn

jaslake,

Nothing seems to be happening. I entered a tenant payment on several days hit ctrl G and nothing happens, what am i doing wrong.

Shawn

OK, perfect (almost). My Excel was updating in the background, now that its done everything seems to be working Except under "Rents Detail"; between "Amount" and "Rent" there is a column labeled "1000"?
and with the sheet protected they cant enter M&O (a manually entered cell)
also how do I un-protect it should they need to change something, I cant give this to my boss without giving them they ability (however un wise) to change something when/if they want, especially once I leave this site.

Shawn

PS your the best! I appreciate your patience and what yo have done for me

Ok, so it appears that column labeled "1000" was and should be the deposits column which should refer to column/row K48 of each corresponding daily tab and the "Deposits Total" should sum up those entries not the rents entries.

I have attached the original that was originally tasked to me to change for clarity.

If its easier, they can be totaled at the bottom of each column, I just moved the totals to the side because the number of rows in each column was so fluid, there was no way of knowing how many rows there is going to be each month.

Shawn