Populating a value from one sheet (not just using the VALUE formula)

Lauren Garvey

Hi

I'm not sure if what I am looking for is possible, but wanted to check.

What I am looking to do is populate specific cell information from one sheet to another with select row information, when a column value is yes. Example sheet that I have been working on below:

So what I am trying to do is if the "Populate order form" column says YES, then the value for "Placed By" column for that specific row would be placed in Cell A on a different sheet. Then repeat the formula with Account Name column into Cell B and so on. This would only be used for one row at a time. Currently we are being asked to put this data into a specific excel file, but if we can have it auto populate to a different sheet that is laid out like the excel file and then exported and emailed, that would be ideal.

I've looked at IF formulas and VALUE formulas and haven't been able to figure out a way to make it work. If it's not possible, that's fine, but would like to see if it is before we move onto an alternative option. Hope that explanation makes sense. If not, I can try and explain further. Thanks so much for anyone and everyone's help!

Andrée Starå

Hi Lauren,

It’s possible.

I’d recommend using VLOOKUP or a combination of INDEX/MATCH.

https://help.smartsheet.com/function/vlookup

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/match

Would that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Genevieve P.

Hi Lauren,

Just to follow-up on Andrée's great advice, I personally prefer the INDEX(MATCH version, however this would normally only match one cell based on one other cell, but you have two values to search for ("Yes" and the Account Name).

An INDEX(MATCH works like this:

=INDEX({Column with value to return in sheet A}, MATCH([Value to match sheet B]@row, {Column with value to match in sheet A}))

There's a few ways you could do this... we could either create a helper column in this sheet that brings the "Yes" and Account Name together in one cell (perhaps with a JOIN formula) and then have a similar value in your second sheet so that the Index(Match could look for this unique identifier and return your contact name. (The Column with value to match has to be identical in both sheets).

Or we could use a different formula called a JOIN(COLLECT formula (which would eliminate the need to create a helper column). The way JOIN(COLLECT works is that you first list the range that has the value you want returned from sheet A, and then list each range and criteria in sheet B afterwards.

Try this:

=JOIN(COLLECT({Sheet A Placed By}, {Sheet A Populate Order Form}, "Yes", {Sheet A Account Name}, [Account Name]@row))

The ranges in {these} are cross-sheet references to the columns in your first sheet, sheet A. The value in [these] represent the Account Name column in sheet B, where the formula is being written. Keep in mind that if you ever have more than one row with the same Account Name and yes, you will return both Placed By cell values into the same cell. (Let me know if I can clarify this further for you).

You can read more about each of these functions in our Help Center: JOIN function / COLLECT function / @row function / Cross Sheet References

Cheers!

Genevieve

Paul Newcome

If you are just filtering this data to be put into an excel file and then emailed, you could use a report that pulls all "Yes" rows and then have that report automatically emailed as an excel attachment. This would completely automate the process and not require the use of another sheet with cross sheet formulas.

Genevieve P.

⬆️

What Paul said!! Here's the Help Article that explains how to send a Report as an emailed Excel file: https://help.smartsheet.com/articles/516096-sharing-sending-reports

Andrée Starå

To add to Paul's excellent solution.

Edit because it's not correct 😉

Beware that if there is any sensitive information in the sheet it will be included in the Excel attachment. If it's important to only share specific information you could create a report from the new sheet created with the VLOOKUP or INDEX/MATCH and then send that one instead.

Paul Newcome

The report could be built to only show those columns that contain information that needs to be sent. If confidential data is to be excluded from the excel sheet, then those columns just don't have to be added to the report.

Andrée Starå

@Paul Newcome Yes, you're correct, but I don't think it's been like this previously, and I also have a memory that we've discussed it before. 🤔

Now it's time for:

https://media.giphy.com/media/3KVRMtLXfORVosk6TW/giphy.gif

Paul Newcome

@Andrée Starå I always remember reports as providing only those columns that are built into the report. It is hidden columns in sheets that will still send. I think that might be the conversation we previously had.

But yes... I agree it is time for...

Lauren Garvey

Thank you so much for all for the helpful and quick feedback! I'm going to try some of the formulas above and see what works!

Andrée Starå

@Paul Newcome Yes, maybe that was it.

https://media.giphy.com/media/687qS11pXwjCM/giphy.gif

Andrée Starå

@Lauren Garvey

Happy to help!

Genevieve P.

Happy to help, @Lauren Garvey! Let us know if you're having any trouble and we'll be here... coffee gifs at the ready.

https://media.giphy.com/media/eWLDhSJ206rUQ/giphy.gif

Paul Newcome

@Lauren Garvey Happy to help! 👍️

Feel free to revisit if you need any help with those formulas. Between @Genevieve P, @Andrée Starå , myself and the gallons of coffee we are apparently drinking, we should be able to help. 😜

Lauren Garvey

So I ended up using a slightly simplified version of the JOIN(COLLECT formula you shared Genevieve and it worked!

I ended up just using

=JOIN(COLLECT({Sheet A - Placed By}, {Sheet A - Populate Order Form}, "yes"))

This allows me to simply put Yes in the populate order form column and it will pull over the values in each column for that specific row. I would not have figured this out on my own so thank you!

Genevieve P.

Glad it worked for you!

However since there's no Account Name specified, if you have more than one "yes" you'll return multiple names in the one cell where this formula is located... is that what you wanted to do? Gather all the data into one cell?

If so you might want to add a delimiter (something to separate the different values):

=JOIN(COLLECT({Sheet A - Placed By}, {Sheet A - Populate Order Form}, "yes"), " / ")

The Collect Help Center article has an example of this: https://help.smartsheet.com/function/collect