Hello,
I have a file that i'm trying to find a way to pull multiple years data off of. I'm using the Index/Match formula to pull my current year data but i have a prior year column i'd like to pull the prior year but not sure how to. I essentially have 2 tabs, one that has my report, the other that has the data. My formula is pulling from the data tab. Column D is the column i need my 2021 data. It's showing 2022 since i haven't figure out what to do yet.
I have a file that i'm trying to find a way to pull multiple years data off of. I'm using the Index/Match formula to pull my current year data but i have a prior year column i'd like to pull the prior year but not sure how to. I essentially have 2 tabs, one that has my report, the other that has the data. My formula is pulling from the data tab. Column D is the column i need my 2021 data. It's showing 2022 since i haven't figure out what to do yet.
DoubleTree Analysis.xlsx | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | January-22 | |||||||
2 | ACT | BUD | PRIOR | BUD VAR | PY VAR | |||
3 | Rooms Available | 4,920 | #N/A | 4,920 | #N/A | 0 | ||
4 | Rooms Sold | 2,403 | #N/A | 2,403 | #N/A | 0 | ||
5 | Occupancy | 48.84% | #N/A | 48.84% | #N/A | 0.00% | ||
6 | ADR | 63.64 | #N/A | 63.64 | #N/A | - | ||
7 | Rooms RevPAR | 31.08 | #N/A | 31.08 | #N/A | - | ||
8 | Operating Revenue | |||||||
9 | Rooms Revenue | 152,935 | #N/A | 152,935 | #N/A | 0 | ||
10 | Food & Beverage Revenue | 67,351 | #N/A | 67,351 | #N/A | 0 | ||
11 | Other Minor Operated Depts. Rev. | 4,330 | #N/A | 4,330 | #N/A | 0 | ||
12 | Total Operating Revenue | 224,616 | #N/A | 224,616 | #N/A | 0 | ||
13 | Departmental Expenses | |||||||
14 | Rooms Expenses | 74,131 | #N/A | 74,131 | #N/A | 0 | ||
15 | Food & Beverage Expenses | 96,284 | #N/A | 96,284 | #N/A | 0 | ||
16 | Other Minor Oper. Depts. Expenses | 1,852 | #N/A | 1,852 | #N/A | 0 | ||
17 | Total Departmental Expenses | 172,267 | #N/A | 172,267 | #N/A | 0 | ||
18 | Total Departmental Profit | 52,349 | #N/A | 52,349 | #N/A | 0 | ||
19 | Undistributed Operating Expenses | |||||||
20 | Administrative & General | 64,709 | #N/A | 64,709 | #N/A | 0 | ||
21 | Information & Telecomm. Systems | 9,304 | #N/A | 9,304 | #N/A | 0 | ||
22 | Sales & Marketing | 45,557 | #N/A | 45,557 | #N/A | 0 | ||
23 | Property Operations & Maint. | 25,859 | #N/A | 25,859 | #N/A | 0 | ||
24 | Utilities/Franchise Royalty Fees | 9,969 | #N/A | 9,969 | #N/A | 0 | ||
25 | Total Undistributed Expenses | 155,398 | #N/A | 155,398 | #N/A | 0 | ||
26 | Gross Operating Profit | (103,049) | #N/A | (103,049) | #N/A | 0 | ||
27 | Management Fees | 5,615 | #N/A | 5,615 | #N/A | 0 | ||
28 | Propert & other Taxes/Insurance | 16,859 | #N/A | 16,859 | #N/A | 0 | ||
29 | FF&E Reserve | 0 | #N/A | 0 | #N/A | 0 | ||
30 | EBITDA | (125,523) | #N/A | (125,523) | #N/A | 0 | ||
DOUBLETREE HOTEL- Tracker |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B3,D3 | B3 | =INDEX('Data - Actuals'!$B$2:$BZ$2,1,MATCH($A$1,'Data - Actuals'!$B$1:$BZ$1,0)) |
C3:C7 | C3 | =INDEX('Data - Actuals'!P3:AA3,1,MATCH($A$1,'Data - Actuals'!$B$2:$M$2,0)) |
E3:E7,E20:E30,E14:E18,E9:E12 | E3 | =B3-C3 |
F3:F7,F20:F30,F14:F18,F9:F12 | F3 | =B3-D3 |
B4,D4 | B4 | =INDEX('Data - Actuals'!$B$3:$BZ$3,1,MATCH($A$1,'Data - Actuals'!$B$1:$BZ$1,0)) |
B5,D5 | B5 | =INDEX('Data - Actuals'!$B$4:$BZ$4,1,MATCH($A$1,'Data - Actuals'!$B$1:$BZ$1,0)) |
B6,D6 | B6 | =INDEX('Data - Actuals'!$B$5:$BZ$5,1,MATCH($A$1,'Data - Actuals'!$B$1:$BZ$1,0)) |
B7,D7 | B7 | =INDEX('Data - Actuals'!$B$6:$BZ$6,1,MATCH($A$1,'Data - Actuals'!$B$1:$BZ$1,0)) |
B9,D9 | B9 | =INDEX('Data - Actuals'!$B$9:$BZ$9,1,MATCH($A$1,'Data - Actuals'!$B$1:$BZ$1,0)) |
C9:C11,C20:C24,C27:C29,C14:C16 | C9 | =INDEX('Data - Actuals'!P10:AA10,1,MATCH($A$1,'Data - Actuals'!$B$2:$M$2,0)) |
B10,D10 | B10 | =INDEX('Data - Actuals'!$B$10:$BZ$10,1,MATCH($A$1,'Data - Actuals'!$B$1:$BZ$1,0)) |
B11,D11 | B11 | =INDEX('Data - Actuals'!$B$11:$BZ$11,1,MATCH($A$1,'Data - Actuals'!$B$1:$BZ$1,0)) |
B12:D12,B17:D17 | B12 | =SUM(B9:B11) |
B14,D14 | B14 | =INDEX('Data - Actuals'!$B$14:$BZ$14,1,MATCH($A$1,'Data - Actuals'!$B$1:$BZ$1,0)) |
B15,D15 | B15 | =INDEX('Data - Actuals'!$B$15:$BZ$15,1,MATCH($A$1,'Data - Actuals'!$B$1:$BZ$1,0)) |
B16,D16 | B16 | =INDEX('Data - Actuals'!$B$16:$BZ$16,1,MATCH($A$1,'Data - Actuals'!$B$1:$BZ$1,0)) |
B18:D18 | B18 | =B12-B17 |
B20,D20 | B20 | =INDEX('Data - Actuals'!$B$20:$BZ$20,1,MATCH($A$1,'Data - Actuals'!$B$1:$BZ$1,0)) |
B21,D21 | B21 | =INDEX('Data - Actuals'!$B$21:$BZ$21,1,MATCH($A$1,'Data - Actuals'!$B$1:$BZ$1,0)) |
B22,D22 | B22 | =INDEX('Data - Actuals'!$B$22:$BZ$22,1,MATCH($A$1,'Data - Actuals'!$B$1:$BZ$1,0)) |
B23,D23 | B23 | =INDEX('Data - Actuals'!$B$23:$BZ$23,1,MATCH($A$1,'Data - Actuals'!$B$1:$BZ$1,0)) |
B24,D24 | B24 | =INDEX('Data - Actuals'!$B$24:$BZ$24,1,MATCH($A$1,'Data - Actuals'!$B$1:$BZ$1,0)) |
B25:D25 | B25 | =SUM(B20:B24) |
B26:D26 | B26 | =B12-B17-B25 |
B27,D27 | B27 | =INDEX('Data - Actuals'!$B$27:$BZ$27,1,MATCH($A$1,'Data - Actuals'!$B$1:$BZ$1,0)) |
B28,D28 | B28 | =INDEX('Data - Actuals'!$B$28:$BZ$28,1,MATCH($A$1,'Data - Actuals'!$B$1:$BZ$1,0)) |
B29,D29 | B29 | =INDEX('Data - Actuals'!$B$29:$BZ$29,1,MATCH($A$1,'Data - Actuals'!$B$1:$BZ$1,0)) |
B30:D30 | B30 | =B26-B27-B28-B29 |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
'DOUBLETREE HOTEL- Tracker'!Print_Area | ='DOUBLETREE HOTEL- Tracker'!$A$1:$M$34 | B3:D7, B9:D11, B14:D16, B20:D24, B27:D29 |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
A1:F1 | List | ='Data - Actuals'!$B$1:$AN$1 |