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

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

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!

Answers

  • Andrée StaråAndrée Starå ✭✭✭✭✭
    edited 03/26/20

    Hi Lauren,

    It’s possible.

    I’d recommend using VLOOKUP or a combination of INDEX/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!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • 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 NewcomePaul 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.

  • ⬆️

    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åAndrée Starå ✭✭✭✭✭
    edited 03/26/20

    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.

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • Paul NewcomePaul 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å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:


    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • Paul NewcomePaul 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...


  • 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åAndrée Starå ✭✭✭✭✭

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    @Lauren Garvey

    Happy to help!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

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


  • Paul NewcomePaul 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. 😜

  • 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 PGenevieve P admin
    edited 03/26/20

    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

Sign In or Register to comment.