Forum Discussion
Help with a formula which uses parameters based on which day / date it is.
I'd convert variables to structured tables. With that formula for Resources -Staffing block could be
=E8 * IF( ISNA(MATCH($B8, BankHolidays[Bank Holidays], 0)) * ($C8 <> "Sunday"), INDEX( RegularRate[Cost / Hourly Rate], MATCH(E$6, RegularRate[Resource], 0) ), INDEX( HolidayRate[Cost / Hourly Rate], MATCH(E$6, HolidayRate[Resource], 0) ) )The rest is similar.
SergeiBaklan I used the proposal you gave and i needed to make some changes. I was hoping i could add another charge (Mobile operator) to the list and expand the list but it hasn't worked (the one you created is locked). I even tried to create a new list and then point the formula to that but that hasn't worked.
Are you able to modify the attached for me to accommodate the new charge ? I note your previous comment about structured tables. Unfortunately i don' have the expertise to implement this.
Thanking you in advance,
Raj
In file you attached data for Resource variable is organized in structured table.
When you add new data at the bottom, in general such table shall be expanded automatically. Not sure why that didn't happen in your case.
However, you may resize structured table manually by dragging bottom right corner of the table.
Or stay within table, on ribbon Table Design->Resize table
Same for holidays plus change duplicated Mobile Driver on Mobile Operative
With that formula works
SergeiBaklan Thank you for the explanation. That worked really well.
Mr_Raj_C , you are welcome, glad to help