Calculate cell value based on various cell look up functions
Hello Everyone,
I am trying to create some automation in my SmartSheet that will automatically calculate the estimated cost for a project based on 1) type of project 2) production hours 2) estimated development hours (based on production hours) 4) how much the cost will be based on hourly cost
For example, I have the column 'Production Hours'. I also have the Course Asset Type identified. How do I create a calculation that looks up the Course Asset Type from the hidden columns, then calculates the Estimated Development Hours & Estimated Development Cost (highlighted fields)?
So the calculation should - Consider Production Hours, Consider Asset Type, Look Up Asset Type in Training Course Assets, Based on the type, calculate the Estimated Development Hours based on Production Hours and Asset Type, then calculate Estimated Development Cost based on Production Hours and Asset Type
Best Answers
-
So is the the INDEX/MATCH you need help with?
=INDEX([Column To Pull]:[Column To Pull], MATCH([Column to match ON]@row, [Column to match IN]:[Column to match IN], 0))
-
Yes! That worked! Thanks so much!
Answers
-
You would use an INDEX/MATCH to grab whatever you need from the tables. You can then add them together or multiply or do whatever you need with them. I'm just not sure exactly how each variable is supposed to impact the final output. Are you adding things together, dividing, subtracting, or multiplying (or a combination of)?
-
Ok, so what I'm trying to do is use the Index/Match to locate the asset type and then calculate the number of hours based on the asset type and production hours. For example in the first highlighted section: INDEX/MATCH eLearning - L1 asset type hours and then multiply the hours by production hours. So it would pull (Index/Match 79 hours) * .25 = 19.75
-
So is the the INDEX/MATCH you need help with?
=INDEX([Column To Pull]:[Column To Pull], MATCH([Column to match ON]@row, [Column to match IN]:[Column to match IN], 0))
-
Side note... I am curious how you came up with your username. My daughter calls me "Pops", and I was born in 1988. It definitely gave me a chuckle when I noticed it.
-
Thanks Paul! The username is coincidental because it's part of my last name, so a play on words. I've always been called that, even as a kid!
-
Almost, I have the Index/Match correct so it pulls the Asset Type. However, what I want it to do is look for Course - Exam and then print the hours (column next to it) in the cell. So I tried this formula an it's giving me a #NO MATCH, but I want it to give me the value 158, since that is the corresponding value next to Course - Exam, which is what I am trying to have the formula find in the table and match to the column next to it, which is 158.
=INDEX([Hidden - Training Course Assets]1135:[Hidden - Training Course Assets]1157, MATCH([Financials - Course Asset Type]@row, [Hidden - Estimated Development Hours]1135:[Hidden - Estimated Development Hours]1157, 0))
-
Try switching the Assets range with the Hours range.
In an INDEX/MATCH, the range you want to pull from should be the first range and the range you want to match on should be the second range.
The way you currently have it written, you are searching for "Course - Exam" in the Hours column.
-
Yes! That worked! Thanks so much!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
