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

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

Find more posts tagged with

Formulas

Project Management

Comments

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?

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…