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

Elias_NIO

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

 

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

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

Elias_NIO

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

Elias_NIO

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

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?