HI All,
to simplfy my formula at cell D54, is there any better formula(index+match) i can use to return the result?
source come from this page:
THANK YOU VERY MUCH FOR YOUR GUIDANCE
to simplfy my formula at cell D54, is there any better formula(index+match) i can use to return the result?
HOTEL.xlsx | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | |||
51 | |||||||||||
52 | up-to-date | 20 | 0 | 0 | |||||||
53 | date | opening quantity | rental quantity (pre booked) | rental quantity (actual) | net balance | return quantity | closing quantity | remarks | |||
54 | Saturday, 1 July 2023 | 20 | 20 | 20 | 20 | ||||||
55 | Sunday, 2 July 2023 | ||||||||||
56 | Monday, 3 July 2023 | ||||||||||
57 | Tuesday, 4 July 2023 | ||||||||||
58 | Wednesday, 5 July 2023 | ||||||||||
59 | Thursday, 6 July 2023 | ||||||||||
60 | Friday, 7 July 2023 | ||||||||||
61 | Saturday, 8 July 2023 | ||||||||||
DLH - DATA |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G52,D52:E52 | D52 | =SUM(D54:D84) |
D54 | D54 | =SUM('DLH - GUEST'!C56,'DLH - GUEST'!C61,'DLH - GUEST'!C66,'DLH - GUEST'!C71,'DLH - GUEST'!C76,'DLH - GUEST'!C81,'DLH - GUEST'!C86,'DLH - GUEST'!C91,'DLH - GUEST'!C96,'DLH - GUEST'!C101,'DLH - GUEST'!C106,'DLH - GUEST'!C111,'DLH - GUEST'!C116,'DLH - GUEST'!C121,'DLH - GUEST'!C126,'DLH - GUEST'!C131,'DLH - GUEST'!C136,'DLH - GUEST'!C141,'DLH - GUEST'!C146,'DLH - GUEST'!C151) |
C55:C61 | C55 | =IF(M54="","",IF(M54="no",H54,IF(OR(L54="/"),$C$54,IF(AND(M54="yes",L54="park to hotel"),(H54+K54),IF(AND(M54="yes",L54="return to park"),(H54+K54)))))) |
F54:F61 | F54 | =IFERROR((C54-E54),"") |
H54:H61 | H54 | =IFERROR((C54-E54+G54),"") |
source come from this page:
HOTEL.xlsx | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
51 | Saturday, 1 July 2023 | Sunday, 2 July 2023 | Monday, 3 July 2023 | ||||
52 | 1 | guest name | |||||
53 | guest contact | ||||||
54 | booking number | ||||||
55 | number of stay | ||||||
56 | pre booked quantity | 10 | |||||
57 | 2 | guest name | |||||
58 | guest contact | ||||||
59 | booking number | ||||||
60 | number of stay | ||||||
61 | pre booked quantity | 10 | |||||
62 | 3 | guest name | |||||
63 | guest contact | ||||||
64 | booking number | ||||||
65 | number of stay | ||||||
66 | pre booked quantity | ||||||
67 | 4 | guest name | |||||
68 | guest contact | ||||||
69 | booking number | ||||||
70 | number of stay | ||||||
71 | pre booked quantity | ||||||
DLH - GUEST |
THANK YOU VERY MUCH FOR YOUR GUIDANCE