Matrix Sheet based on date and personal resource assigned

Enoch Mak

Hello all

I'm a bit stumped by this and would like assistance on how to create my formulas. I'm tasked to create a sheet, view, or dashboard to summary our master data sheet turning it into a matrix style sheet. This matrix style sheet will pull data from several other databases within smartsheets.

The matrix should reference the following
- check events between the start of a work week and end of a work week
- check person 1, 2, 3, 4…etc.. is assigned within that week
- pull the data for location- event putting them into a single cell within the matrix based on matching it with the work date and personal resource assigned

Are there any formulas or I could use that'll help me with starting this?

Dan Curl

Hard to give you a more detailed answer without knowing what source data you are pulling from. The only data source I can really imagine you are using is an events list. What else is there?

Here is a formula I used that might get you on the right track though.

Situation - I have a list of projects and a list of invoices in two separate sheets. A project has an "AFE" number and is listed on each invoice record. A Project can have multiple invoices.

On the project list I wanted to have a listing of the Invoices in one cell.

=IF(AFE@row = "", "", JOIN(COLLECT({invoices_woandstatus}, {Invoices_AFE}, AFE@row), "; " + CHAR(10)))

IF(AFE@row = "", "", || This just ensures my row has a unique ID.

COLLECT({invoices_woandstatus}, {Invoices_AFE}, AFE@row)

• This takes all the values in my invoices table {invoices_woandstatus} and pull the values where the AFE Column in the invoices table matches my AFE for my project row (AFE@row)

JOIN(COLLECT(……, "; " + CHAR(10))

• This Joins the cells in the Collection with a ";" delimiter and Char(10) which is a line break.

Result is like this

[AFE INV | STATUS | INV and Status]

[PROJ-123 | INV123 | Submitted | INV123 - Submitted]
[PROJ-123 | INV245 | Exported | INV245 - Exported]

The formula would spit out

"INV123 - Submitted;
INV245 - Exported"

Hope this helps some.

Enoch Mak

@Dan Curl thanks this actually helped me out in creating my own formula for it

I ended using some helper sheets and columns to seperate some of the data on my end back to a more workable form. For Join i ended up joining the Location and Event text in a helper column to simplify the main formulas in the matrix sheet.

=IFERROR(INDEX(COLLECT([Location and Event]:[Location and Event], [Column23]:[Column23], [Week #]@row, [Column17]:[Column17], MATCH("Person 1", [Column17]:[Column17], 0), 1), ""))