SOLVED

Need experienced assistance with above my paygrade formula

Copper Contributor

I hope I describe this correctly. I am a 12-unit condo-hotel which has 7 private owners, all receiving a percentage of the hotel income. I have a workbook WB1 all set with formulas, dates, etc. My second workbook WB2, is generated at the end of each month and contains the dates the guest stays, arrival to departure (remember for later departure date has no income from guest), room number, Average Daily Rate (ADR) and total nights stays, amongst many other categories. What I would like to do is not have to manually enter the ADR into WB1 (highlighted area) and just have the ADR copied from WB2 into the corresponding dates. I tried a helper column, vlookup, but like I said, beyond my paygrade. Can anyone assist me? I really appreciate your time and patience, understanding. 

 

 

WB2WB2WB1WB1

2 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

@PCHotel  I think I get what you want.  I used SUMPRODUCT and I used SUMIFS as 2 examples and I did it using Table Reference (recommended) and regular cell refs.  The advantages with the table refs are a) you have an idea of what values you are working with and b) as your table gets bigger so does the reference range (i.e. in the cell ref I arbitrarily went up to row 100 but if the table grows past that you will need to edit it to be a bigger range but you don't want to use the whole column and have excel get overloaded (if your sheet get big)

so for rooms 1&2 are sumproduct using table ref, 4&5 are sumproduct using cell refs, 7 is sumifs using table refs, and 9 is sumifs using cell refs.

Hope this helps.

@mtarler OH MY GOSH you are amazing, I am actually emotional. Thank you so much I can not believe your kindness and assistance. You truly are amazing. May God continue to bless you and thank you so much for your time and assistance.

My kind regards,

Jay

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@PCHotel  I think I get what you want.  I used SUMPRODUCT and I used SUMIFS as 2 examples and I did it using Table Reference (recommended) and regular cell refs.  The advantages with the table refs are a) you have an idea of what values you are working with and b) as your table gets bigger so does the reference range (i.e. in the cell ref I arbitrarily went up to row 100 but if the table grows past that you will need to edit it to be a bigger range but you don't want to use the whole column and have excel get overloaded (if your sheet get big)

so for rooms 1&2 are sumproduct using table ref, 4&5 are sumproduct using cell refs, 7 is sumifs using table refs, and 9 is sumifs using cell refs.

Hope this helps.

View solution in original post