Moving Columns to Other sheets

Options

Is there a way to move only specifc columns and not the entire row into other sheets and/or when a specific cell is updated in one sheet that cells info ONLY can be copied to another ?

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @lesmickin

    @Andrée Starå is absolutely correct! It sounds like an INDEX(MATCH formula would be the best way to gather this data. The INDEX portion will look through the Date column that you want returned, and the MATCH function will compare your two Case Number columns to see if there is a match between the sheet.

    Like this:

    =INDEX({Date Column Unit Sheet}, MATCH([Case Number]@row, {Case Number Unit Sheet}, 0))

    I would also throw in an IFERROR function around the formula so that if there isn't a match, it returns "" (blank) instead of an error.


    Try this:

    =IFERROR(INDEX({Date Column Unit Sheet}, MATCH([Case Number]@row, {Case Number Unit Sheet}, 0)), " ")


    Here are some Help Articles I used that you may find helpful: Cross Sheet Formulas / INDEX Function / MATCH Function / IFERROR Function


    Let me know if this works for you or if you need any further clarification!

    Cheers,

    Genevieve

Answers

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

    Hi @lesmickin

    I hope you're well and safe!

    Yes.

    You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

    Another option would be to use so-called helper sheets. In short, copy the row to a helper sheet and then use my method described previously to get the values you need to another helper sheet and then copy/move the row from that sheet to the main destination sheet.

    Would any of those options work/help?

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) 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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • lesmickin
    Options

    Thank you Andree,

    I was beginning to think I could use that function. My "coding" skills aren't the greatest so am trying to figure out the correct formula to use to ensure I get the right data.

    I have a mastersheet where I keep all relevant information. I have another unit that updates info on their sheet and i need the info they enter to be automatically entered onto mine (without transferring everything over, hence just needing the specifc cell in the column)

    For instance. I need the date accepted from the unit sheet to be entered into the mastersheet.

    Columns are named the same on both sheets but the data on both are different (master of course has more info than the unit)

    If case# matches the case# on the unit sheet, and the date accepted on the unit sheet is entered, then the date accepted on the master would then be autofilled. Otherwise it would be left blank (on the master)

    so not sure what formula I should be using

    any help/guidance would be GREATLY appreciated

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @lesmickin

    @Andrée Starå is absolutely correct! It sounds like an INDEX(MATCH formula would be the best way to gather this data. The INDEX portion will look through the Date column that you want returned, and the MATCH function will compare your two Case Number columns to see if there is a match between the sheet.

    Like this:

    =INDEX({Date Column Unit Sheet}, MATCH([Case Number]@row, {Case Number Unit Sheet}, 0))

    I would also throw in an IFERROR function around the formula so that if there isn't a match, it returns "" (blank) instead of an error.


    Try this:

    =IFERROR(INDEX({Date Column Unit Sheet}, MATCH([Case Number]@row, {Case Number Unit Sheet}, 0)), " ")


    Here are some Help Articles I used that you may find helpful: Cross Sheet Formulas / INDEX Function / MATCH Function / IFERROR Function


    Let me know if this works for you or if you need any further clarification!

    Cheers,

    Genevieve

  • lesmickin
    Options

    Thank you so much. it worked perfectly~

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    No problem! Happy to hear that it worked for you. 🙂

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

    @lesmickin

    Excellent!

    You're more than welcome!

    Remember! Did my post(s) help or answer your question or solve your problem? Please support 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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.