Symbol, Checkbox, Percent Complete formulas
I have a series of projects listed in a sheet (Sheet 1), each of the projects has a deliverable name. For each deliverable name I have a Symbol, a Checkbox, a Percent Complete, and the Person Accountable.
In a separate sheet (Sheet 2) I want to return the results of the Symbol, the Checkbox, the Percent Complete, and the Person Accountable based on the deliverable name.
What formulas would I use to identify each. I understand I need to reference Sheet 1 on Sheet 2 and I have the deliverable name listed in Sheet 2.
Thank you very much!
Best Answers

Depending on how your sheets are arranged, you can do this with either a VLOOKUP or an INDEX/COLLECT and some cross sheet references.
For example, to pull the symbol from Sheet 1 into Sheet 2 based on the Deliverable Name (where the cross sheet references in { } brackets refer to the column on sheet 1):
=INDEX(COLLECT({Symbol}, {Deliverable Name}, [Deliverable Name]@row), 1)
To do it as a VLOOKUP, based on your deliverable name and symbol being next to one another in sheet 1:
=VLOOKUP([Deliverable Name]@row, {Deliverable NameSymbol}, 2, false)
Sample data:
Sample output:
For the other columns (checkbox etc.) you would either change the crosssheet column reference to the relevant one, or amend the range/column number for the VLOOKUP. If you were using a VLOOKUP then it is very easy to make a range which covers everything and just amend the number in the formula to the correct one for number of columns to count over.
A VLOOKUP will rely on your Deliverable Name being to the left of any additional data (which may or may not be a problem), while the INDEX will function regardless.
If you're unsure about how to make a crosssheet reference, here are a couple of things which may help:
Hope this helps, but if you've any problems/questions then let us know!

Thank you, that worked.
Have a second for an additional question?
If I have a series of these deliverables under an objective now (for example 5), and I want to rate the entire objective based on the highest risk in my Symbols (Red being the highest risk, then Yellow, Green, Gray) what would be the formula. Same thing for the objective checkbox, if one check box is selected the objective is a checkbox.
blob:https://community.smartsheet.com/7bb58196f6d14a93a0e6104b093cfd8e
Answers

Depending on how your sheets are arranged, you can do this with either a VLOOKUP or an INDEX/COLLECT and some cross sheet references.
For example, to pull the symbol from Sheet 1 into Sheet 2 based on the Deliverable Name (where the cross sheet references in { } brackets refer to the column on sheet 1):
=INDEX(COLLECT({Symbol}, {Deliverable Name}, [Deliverable Name]@row), 1)
To do it as a VLOOKUP, based on your deliverable name and symbol being next to one another in sheet 1:
=VLOOKUP([Deliverable Name]@row, {Deliverable NameSymbol}, 2, false)
Sample data:
Sample output:
For the other columns (checkbox etc.) you would either change the crosssheet column reference to the relevant one, or amend the range/column number for the VLOOKUP. If you were using a VLOOKUP then it is very easy to make a range which covers everything and just amend the number in the formula to the correct one for number of columns to count over.
A VLOOKUP will rely on your Deliverable Name being to the left of any additional data (which may or may not be a problem), while the INDEX will function regardless.
If you're unsure about how to make a crosssheet reference, here are a couple of things which may help:
Hope this helps, but if you've any problems/questions then let us know!

Thank you, that worked.
Have a second for an additional question?
If I have a series of these deliverables under an objective now (for example 5), and I want to rate the entire objective based on the highest risk in my Symbols (Red being the highest risk, then Yellow, Green, Gray) what would be the formula. Same thing for the objective checkbox, if one check box is selected the objective is a checkbox.
blob:https://community.smartsheet.com/7bb58196f6d14a93a0e6104b093cfd8e 
Thank you Nick. Got it all done.

For your symbol column it would be something along these lines:
=IFERROR(IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIF(CHILDREN(), "Green") > 0, "Green", IF(COUNTIF(CHILDREN(), "Gray") > 0, "Gray", INDEX(COLLECT({Symbol}, {Deliverable Name}, [Deliverable Name]@row), 1))))), "")
Data (sheet 1(:
Output (sheet 2):
If you wanted something slightly different (e.g. blanks to appear grey) then the formula could be amended. Hopefully this also gives you some ideas for the checkbox column, and I'm guessing the percentage would just be an average.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.8K Get Help
 65 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 56 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!