Determine proof dates based on asset lead times
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.
Best Answer
-
Happy to help. 👍️
Answers
-
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))
-
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?
-
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?
-
Are you able to provide a screenshot of the table?
-
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.
-
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?
-
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)
-
Each line in sheet would be from a different department there wouldn't be multiple departments on a line. Each is a separate job #
-
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?
-
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.
thank you
-
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?
-
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.
-
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))
-
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.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!