Scenario:
Problem:
Any suggestions?
You could actually use an IF statement for this. In this example I will say we are using a column called [Surveyed?] that is a checkbox type. We will write a formula that will search your [Company Name] column in the Master Sheet (the one that the forms populate) and check the box on your Metrics Sheet (the static list) if the company name in the [Company Name] column of your Metrics Sheet is listed.
IF(ISTEXT(JOIN(COLLECT({Master Sheet Company Name Column}, {Master Sheet Company Name Column}, @cell = [Company Name]@row))), 1)
What this does is COLLECT all cells in the {Master Sheet Company Name} column that match whatever is in the [Company Name] column in your Metrics Sheet for whatever row the formula is on.
It will then join all of those cells that match together. If there are no cells that match, the JOIN/COLLECT will return a blank (meaning the company name isn't on the master sheet).
If it is blank when there are no matching cells, then it means that if there are matching cells, the JOIN/COLLECT will return a string of text (the company name repeated however many times it is found on the Master Sheet).
Since we don't care how many times it shows up, just that it shows up, we wrap that JOIN/COLLECT in an ISTEXT function and use that as our logical statement in our IF function.
So basically this formula is saying that if any text is returned by joining all cells that are collected from the master sheet range that match the company name here on the metrics sheet, then check the box.
.
We can also use:
=IF(FIND([Company Name]@row, JOIN({Master Sheet Company Name Column}, " - ")) > 0, 1)
This basically joins the ENTIRE [Company Name] column on the master sheet and separates it all out using a hyphen in one loooooong string of text. It will then search that entire string. If it finds the appropriate company name anywhere, then it will check the box.
There are numerous other options that will achieve the desired result, but these are the two most straightforward options that I can think of with the second actually being more straightforward than the first.
Thanks! I couldn't get option #1 to work (is the "{Master Sheet Company Name Column}" phrase supposed to be duplicated?)... but option #2 worked perfectly... .thank you very much!
-KR
Happy to help. For option 1, yes. The same range is repeated. Glad you were able to get something working though.
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
am I able to separate this into multiple rows? in a new sheet? Desired Result -
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.