Update Cells from Certain Tabs based on Value other cells

Erika7

New Member
Joined
Oct 14, 2013
Messages
3
I have a workbook with 1 tab per calendar day for basic Hotel/guest management system. Each tab has a table to manage daily check in / check out info & payments made by customers. I want to be able to have a formula on each tab that recognizes the check in & check out date and:
1) Updates all future tabs (dates) until that customer checks out with their name & rate info so it doesn't have to be copied each day
2) Have a running balance on their balance that's carried over daily.

There's only 15 rooms so each tab doesn't get too busy, however, it's difficult to track each customers payments since many of them stay for over 1 week and sometimes multiple months and their payments aren't consistent. 1 person may pay daily cash/credit card, another cash every few days as a lump sum. This being said, need to be able to easily see on each day's tab which customer is up-to-date or behind on payments.

Example scenario with 3 customers over 3 days (Note changes in Name Info, Prev. Balance & Balance columns based on payments):

1st Tab: Jan 1
RoomFirstLast Check InCheck Out# NightsNightly RateTotal RateAmount PaidPrevious BalanceBalance
1JohnSmith1/11/43$25$75$25$0$50
2JohnDoe1/11/21$30$30$30$0$0
3JaneDoe1/11/32$25$50$30$0$20

<tbody>
</tbody>









2nd Tab: Jan 2
RoomFirstLast Check InCheck Out# NightsNightly RateTotal RateAmount PaidPrevious BalanceBalance
1JohnSmith1/11/43$25$75$0$50$50
2
3JaneDoe1/11/32$25$50$20$20$0

<tbody>
</tbody>









3rd Tab: Jan 3
RoomFirstLast Check InCheck Out# NightsNightly RateTotal RateAmount PaidPrevious BalanceBalance
1JohnSmith1/11/43$25$75$50$50$0
2
3

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Watch MrExcel Video

Forum statistics

Threads
1,118,765
Messages
5,574,105
Members
412,571
Latest member
Ventura7
Top