Fill sheet based on another sheet (if cells contain value)

Elias_NIO
Elias_NIO
edited 12/09/19 in Formulas and Functions

Hi Smartsheet community,

I am looking for formulas or ways to automatically fill sheet B with the information of certain columns of sheet A for all rows in sheet A that contain a value. In a dynamic way, meaning if a row is added to sheet A it should be also added to sheet B. For example:

- Sheet A has 1 column with 10 rows containing a value

- I want these exact same values of sheet A row 1 to 10 to be copied into sheet B (also into rows 1 to 10)

- if I add a value to row 11 in sheet A, then this value should be added to sheet B row 11 as well (the same should happen if I delete a value in sheet A, it should be deleted in sheet B)

 

Cell linking does not work here, since it sheet B will not contain a cell link if sheet A does not have a value.

Any ways to use the VLOOKUP formula together with IF and IFBLANK? I tried something like this without success: 

=VLOOKUP(IF(ISBLANK({Sheet A Range 1}@row),{Sheet A Range 1}@row,),{Sheet B Range 1},1,FALSE)

 

Any way to make this happen? Thanks in advance!

Elias

Comments

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

    Hi Elias,

    How many rows and columns would you need to be able to connect/link?

    Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

    I hope that helps!

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    work-bold

    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.

  • Hi Andrée,

    thanks for your Quik reply.

    I created a “test sheet” to avoid including confidential data (see attached screenshots of this sheet). Basically, I have a sheet that I want to use as a card view (see screenshot 1) to have different stakeholders adding data to the sheet using “add a card”.

    If you go to the second screenshot, you see the same sheet as “grid view”. Here you see some columns in grey that I want to use to do calculations but I do not want the user to see when they edit the card view. Therefore, I would like not to include the grey columns in this sheet but have a second sheet where I perform this calculations. To do this, I need all information of the first sheet to be copy pasted into another sheet. Whenever a card is added/deleted/changes in sheet 1 I would like to have sheet 2 automatically updated.

    I am looking at roughly 50 to max 100 rows in sheet 1.

    Any ways to do this using functions and formulas? Cell linking would only work if the amount of row in sheet 1 does not change, correct?

    Many thanks again and looking forward to your reply.

    Elias

    E5AE657B-7435-4AEF-876F-F0FE9BA89577.jpeg

    EB68F03B-3F3D-4CB9-8BC7-0B6A2A8E85DF.jpeg

  • Anyone who has the same problem and knows a solution? Would be very much appreciated! Thanks 

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

    Happy to help!

    The simplest method would be to add an auto number column and then use that for the VLOOKUP formula.

    Would that work?

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!