Jun 13 2022 04:03 PM
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.
Jun 13 2022 07:08 PM
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.
Jun 14 2022 02:55 AM
@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
Jun 13 2022 07:08 PM
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.