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>
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top