Struggling to produce the correct formula

MirekWentylek

Hi All,

First of all thanks for your help!

I'm trying to get the right data from a separate spreadsheet, but struggling to come up with the right way to get that data.

I have a listing of staff and their recorded work time split by weeks:

I'm trying to get their 'Contracted Hours' number as is recorded below. For each week in the staff listing I want the corresponding number of contracted hours to be pulled.

I've recorded the first and last week of when a member of staff worked a number of hours - wanted to use 'Week ID' as a merging factor.

Week Number and Week ID are taken from the sheet listed below as the HR system only provides this 'MM/DD/YYYY - MM/DD/YYYY' format.

Cheers!

Nick Korna

Hi @MirekWentylek,

You would be able to do this with a MIN COLLECT for the FirstWeek & FrWeekNum columns. To then get the FrWeekID, you can use an INDEX COLLECT function from the FirstWeek columns.

For the FrWeekNum formula it would look something like this, as an example:

=MIN(COLLECT({Week Number}, {User}, User@row))

Your FrWeekID would be along the lines of:

=INDEX(COLLECT({Week ID}, {Date}, [First Week]@row), 1)

For the Lastweek etc. it would be very similar, except instead of MIN you would use MAX.

This involves using cross sheet formulas which you need to define the cross-sheet part for (hence the above formulas won't just simply cut & paste). If you've not used these before, then the below should help explain how to do them:

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

Hope this this of some assistance, but if you've still got any problems/questions then just let us know!

MirekWentylek

Many thanks for your feedback Nick. I've managed to get some of it to work:

=INDEX(COLLECT({Working-hours-matrix ContraHours}, {Working-hours-matrix Range 4}, [Week ID]@row, {Working-hours-matrix Range 2}, User@row), 1)

this only works for the rows Week ID 53 and Week ID 56 for STAFF A and Week ID 53 and Week ID 55 for STAFF B:

The idea behind the working hours matrix is that when someone changes their working hours, it is reflected by stating which week they have started working an X number of hours and which week they have stopped working X number of hours, then first week when they've started working a Y number of hours and so on. I guess I would need additional 'logic' that checks whether a Week ID is within range where a member of staff worked X hours or whether it is within range where they worked Y number of hours. Have you got any ideas on how to code that? Listing a number of hours worked by each staff for each individual week seems very manual and ineffective.

Nick Korna

Yes, you would amend your COLLECT to check the week number (or ID, either works) along the lines of:

=INDEX(COLLECT({Staff Contracted Hours}, {Staff First Week}, <=Week@row, {Staff Last Week}, >=Week@row), 1)

This should pull the hours through correctly.

I'd also suggest enclosing your formulas in IFERRORs if you have some lines which aren't going to have data (to stop the #NO MATCH & #INVALID VALUE appearing). These are pretty simple to do. For example, the above formula would be:

=IFERROR(INDEX(COLLECT({Staff Contracted Hours}, {Staff First Week}, <=Week@row, {Staff Last Week}, >=Week@row), 1),"")

Hope this fixes things up for you!

MirekWentylek

Many thanks @Nick Korna! It is working now :)

Below is the complete formula that I've used:

=IFERROR(INDEX(COLLECT({Working-hours-matrix ContraHours}, {Working-hours-matrix FrWeekID}, <=[Week ID]@row, {Working-hours-matrix LastWeekID}, >=[Week ID]@row, {Working-hours-matrix Range 2}, User@row), 1), "Error")