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 Name-Symbol}, 2, false)
Sample data:
Sample output:
For the other columns (checkbox etc.) you would either change the cross-sheet 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 cross-sheet 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/7bb58196-f6d1-4a93-a0e6-104b093cfd8e
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 Name-Symbol}, 2, false)
Sample data:
Sample output:
For the other columns (checkbox etc.) you would either change the cross-sheet 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 cross-sheet 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/7bb58196-f6d1-4a93-a0e6-104b093cfd8e -
-
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
Check out the Formula Handbook template!
