Index Match Values Based on Certain Parent Rows

First, awesome community! I've been looking all over for a solution to my problem, but cannot find anything that would translate to my situation. Hopefully I make some sort of sense describing my issue.

Brief:

I work for a construction company that specializes in high rise construction. For my department, I'm tracking progress and costs broken down by Project, Floor, and Scope of work, i.e (Project A > 1st Floor > Concrete >...) and would like to pull values specific to floor and scope.

I started with a basic Index formula, but can't figure how to make the match function work within those parameters --Maybe I need to use a different approach?

=INDEX({UID1}, MATCH(Type2, {TASK1}, 0)) * Quantity2

This brings the value that I need, but this would force me to tweak and monitor each entry due to the varying amount of Floors and Tasks we perform; I would like a formula that would be able to return the red values in the screen shot based upon matching the Type (A,B, or C) with an identified Task and/or Group.

I hope this makes some type of sense, been racking my brain all weekend lol.

Thanks!

Screen Shot 2019-09-03 at 10.31.30 AM.png

Screen Shot 2019-09-03 at 10.55.48 AM.png

Find more posts tagged with

Construction

Formulas

Project Management

Account and User Management

Comments

Paul Newcome

So you are trying to pull from the first screenshot based on the Group, Task, and Type in the second screenshot?

xwallace

Hey Paul, yes! That’s exactly what I’m going for!

Paul Newcome

Ok. My first suggestion is to add the word "Task" in your second sheet in the Task column. That way we can have some consistency with the data.

You could then add a helper column to both sheets. I'll call it "Helper" for this example.

In the first sheet you would use

=JOIN(ANCESTORS(ID@row), ";")

In the second sheet, it would be

=JOIN(Group@row:Type@row, ";")

This will give you matching data on both sheets that we can use to match against.

=INDEX({First Sheet UID1 Column}, MATCH(Helper@row, {First Sheet Helper Column}, 0)) * Quantity@row

Sign in to join the conversation!

Quick Links

Trending discussions

How to split contents from a cell from a smartsheet into another smartsheet
I need to extract and split the contents of a cell in a Smartsheet into three separate columns to a new smartsheet. The cell entries are originally submitted through a form.
Information in a form populates specific cell.
Hello, Just investigating an idea. I'm building a staff register sheet. I'm curious to know if its possible to populate specific cells by the answers given in a form? So if, in a form, a staff member gives their name, the date and that they will be on leave, sick, training ect. Once the form is submitted, it will populate…
A multiple SUMIF function
My current formula is below. I would like to update this so that I only get a total of those that have been Quoted and have a timeframe data of 2025. How do I add this extra filter into the formula? =SUMIF(Status:Status, "Quoted", [Est. GP]:[Est. GP])