Determine proof dates based on asset lead times

Pam Dunn

I want to determine a 1st proof due date based on asset type and days to complete. I have another Smartsheet listing all asset types and # of days. I have various columns that list asset choices. when any of these columns have an asset type listed I want to reference the other sheet pull in the days and have it provide a date the 1st proof should be completed by. this is so complicated not sure if I can achieve this. I am using a grid not a project plan to display the information at a high level.

Paul Newcome

Happy to help. 👍️

Paul Newcome

You are going to want to use an INDEX/MATCH to pull the number of days to add to the base date.

=[Base Date]@row + INDEX({Other Sheet # Of Days Column}, MATCH([Asset Type]@row, [Other Sheet Asset Type Column}, 0))

Pam Dunn

What if I want to simplify this. Search for the asset type in a range of @row such as [ADS Assets]@row:[Executive Requests]@row then populate new column called Design Time with the # days from the reference sheet?

Pam Dunn

Paul,

I got this formula to work for a single column, but I need to be able to have it "look at a range of columns" match the asset type and then give me the # of days. Is this possible?

Paul Newcome

Are you able to provide a screenshot of the table?

Pam Dunn

Here is a screen shot of the table I want to pull the # of days from. Is that what you wanted to see?

then I have my columns set up by department with drop downs with asset types. So when someone requests an asset it looks like this in a cell.

the multiple assets will be put on separate lines after the project request is reviewed.

Paul Newcome

Ok. So will you need to add up all of the days for all of the assets on a single row? If Facebook and Instagram are selected, then would you need (for example) 5 days for Facebook PLUS 5 days for Instagram?

Pam Dunn

I need the formula to look at all department columns, determine there is an asset type then bring in the # of days.

Here is my initial formula, how can I get the formula to look at each column ? I have 12 departments (columns)

Pam Dunn

Each line in sheet would be from a different department there wouldn't be multiple departments on a line. Each is a separate job #

Paul Newcome

But what about that Sales-Advertising Assets column in your screenshot? using the Outlet Assets column, is "Tent Card" in the table you want to pull from?

Pam Dunn

Yes I have 113 unique asset types with various lead times on a reference sheet.

I have 12 department columns in my main document.

How do I get the formula to look at each column to see if there is a match to an asset type in my reference sheet and bring in the lead time number to my main document.

I apologize if I'm not explaining my situation well.

I found a similar scenario you helped another user with but it was counting instead of pulling in a number maybe this will help to explain what I'm trying to achieve.

https://community.smartsheet.com/discussion/39821/using-nested-match-index-across-multiple-columns

thank you

Paul Newcome

Ok. I am understanding what you are wanting to accomplish. Now for a couple of details...

Across the 12 columns, will there be multiple options selected in either multiple columns on the same row or multiple choices in the same cell?

Pam Dunn

The sheet is a marketing creative jobs request. Each row is a single job request with an automated job # assigned from a request form.

There will not be multiple department columns on same row

There will be multiple options in a cell for a particular department column, but those will be moved to children rows with the initial job# row as the parent so each asset type will be in its own row.

Paul Newcome

Got it. Try this...

=[Base Date]@row + INDEX({Other Sheet # Of Days Column}, MATCH(JOIN(COLLECT([1st Column]@row:[Last Column]@row, [1st Column]@row:[Last Column]@row, @cell <> "")), {Other Sheet Asset Type Column}, 0))

Pam Dunn

Thank you Paul. I'll work on it this afternoon and get back with you on the results. Appreciate your assistance I'm a newbie to formula writing.

Paul Newcome

Happy to help. 👍️