Copy cell to another sheet if drop down is selected.

JayL

Hi,

I'm very new to Smartsheet.

In Sheet A I have columns Project # and Type(dropdown). If Type dropdown value is "New Project" I want the value of Project # to be copied to a new row on Sheet B. I've figured out how to do this within a single sheet using IF, but when I try it using cross sheet references I get an an Unparsable error. What am I missing?

=IF({Estimating Queue Types}@row="New Project",{Estimating Queue Project Num}@row,"")

KPH

Hi @JayL

I think I understand what you are doing and will try to explain. An IF is not going to generate a list for you. You would need to make a lot of separate IFs. I have a couple of ideas and it might be worth playing with them so you can see what they do.

Idea 1

You could set up a cell link between sheet A and sheet B.

Pros -

1. The data in sheet B will match sheet A and when things change in sheet A, the change will be reflected in sheet B.
2. This is quick and easy.

Cons -

1. Sheet B will include all rows not just those where the drop down shows "New Project".
2. Data in sheet A might extend beyond the range that you have linked.

Workaround for con 1 - you can also link the Type column and apply a filter to only show rows where Type is "New Project".

Question -

Will sheet B then look just like sheet A? If so, my response would be to ask if you can do whatever your longer term MATCHing intentions are from sheet A and skip the creation of sheet B.

To see this for yourself

• On sheet B select a range of cells, say 10 rows, in both the Project # and Type columns.
• Right click.
• Select Link from Cell in Other Sheet.
• Select sheet A and choose the same cells. The selection must be the same width and length.

You might stop here. But if not, here is another approach.

Idea 2

You could use an INDEX function to show the data from sheet A in sheet B.

Pros -

1. The data in sheet B will match sheet A and when things change in sheet A, the change will be reflected in sheet B.
2. Sheet B can be restricted to include only rows where the drop down on sheet A shows "New Project".

Cons -

1. This is more slightly difficult to set up.
2. Data in sheet A might extend beyond the rows that you have set up in sheet B (but this is easier to manage than with idea 1).

To do this

Create a column to hold row numbers

You need to define which row on sheet B to put the value that is coming from sheet A. You can't just say the next empty space, you need a row identifier. You can create your own by adding an Auto-Number column to Sheet B. To do this create a column and choose the Colum Type # Auto number, like this:

Create an INDEX formula

In the column that you want the Project Number to appear in (I call this Project #s from sheet A) you can use an INDEX formula, like this:

=INDEX({sheet A Project #}, [Row Number]@row)

Where sheet A Project # is the cross sheet reference to the entire Project # column in sheet A. Let me know if you need help setting up a cross sheet reference. I think you almost had this yesterday.

If sheet A looks like this:

Sheet B would look like this:

Note - You can convert the Project #s from sheet A formula to a column formula so it will just keep working. However, the row numbers will only be added when rows are added to sheet B (not when they are added to sheet A) so you will need to make sure you add some spare rows.

Restrict the values returned based on the dropdown list

So far, we have done nothing about the Type column. I wanted to explain INDEX first. Now we have that working we can change the first part of the index. Rather than selecting the entire column as a range, we can use a COLLECT function to just COLLECT the values that meet a criteria.

That formula would look something like this:

=INDEX(COLLECT({sheet A Project #}, {sheet A Type column}, ="New Project"), [Row Number]@row)

Where sheet A Type column is a cross sheet reference to the Type column in sheet A.

The COLLECT function is collecting values from sheet A Project # where Sheet A Type column is equal to "New Project".

Now sheet B looks like this:

Remove the error messages

From row 4 onwards this does not look good. There is no data to display here and an error messages appears instead. It looks ugly and if you intend to do some MATCHing on this list later, any errors here will prevent those formulas from returning any matches even those that do exist. So we need to get rid of it.

We can do that by wrapping the formula in an IFERROR and returning "" (nothing) instead of the error message:

=IFERROR(INDEX(COLLECT({sheet A Project #}, {sheet A Type column}, ="New Project"), [Row Number]@row), "")

I recommend making that formula a column formula (and adding a lot of blank rows).

Let me know how you get on.

KPH

Welcome @JayL

This is not the correct way to reference a cell in smartsheet:

{Estimating Queue Types}@row

Your cross sheet reference will either be a column or a single cell and will always be referenced using just the part in the curly brackets.

{Estimating Queue Types}

If you fix that you should remove the unparsable error. But I am not sure if the IF will do what you want it to do. INDEX MATCH or an automation to copy the row might be a better solution. If you find IF is not working, please share a little more. Specifically:

1) Do you want the project number to actually copy and be static or do you want it to show what is in sheet A (in other words, if the cell in sheet A changes should the value in sheet B also change)?

2) Which row should the value from sheet A appear on, on sheet B?

JayL

thanks for the response.

I'm trying to dynamically show what is in Sheet A, not static copy. I'm trying to get the New Project #'s onto sheet B so that i can use them to MATCH the other data that I need.

The row doesn't matter as I'll be sorting by this column.

I'm not sure if this is the best way to think about.

KPH

Hi @JayL

I think I understand what you are doing and will try to explain. An IF is not going to generate a list for you. You would need to make a lot of separate IFs. I have a couple of ideas and it might be worth playing with them so you can see what they do.

Idea 1

You could set up a cell link between sheet A and sheet B.

Pros -

1. The data in sheet B will match sheet A and when things change in sheet A, the change will be reflected in sheet B.
2. This is quick and easy.

Cons -

1. Sheet B will include all rows not just those where the drop down shows "New Project".
2. Data in sheet A might extend beyond the range that you have linked.

Workaround for con 1 - you can also link the Type column and apply a filter to only show rows where Type is "New Project".

Question -

Will sheet B then look just like sheet A? If so, my response would be to ask if you can do whatever your longer term MATCHing intentions are from sheet A and skip the creation of sheet B.

To see this for yourself

• On sheet B select a range of cells, say 10 rows, in both the Project # and Type columns.
• Right click.
• Select Link from Cell in Other Sheet.
• Select sheet A and choose the same cells. The selection must be the same width and length.

You might stop here. But if not, here is another approach.

Idea 2

You could use an INDEX function to show the data from sheet A in sheet B.

Pros -

1. The data in sheet B will match sheet A and when things change in sheet A, the change will be reflected in sheet B.
2. Sheet B can be restricted to include only rows where the drop down on sheet A shows "New Project".

Cons -

1. This is more slightly difficult to set up.
2. Data in sheet A might extend beyond the rows that you have set up in sheet B (but this is easier to manage than with idea 1).

To do this

Create a column to hold row numbers

You need to define which row on sheet B to put the value that is coming from sheet A. You can't just say the next empty space, you need a row identifier. You can create your own by adding an Auto-Number column to Sheet B. To do this create a column and choose the Colum Type # Auto number, like this:

Create an INDEX formula

In the column that you want the Project Number to appear in (I call this Project #s from sheet A) you can use an INDEX formula, like this:

=INDEX({sheet A Project #}, [Row Number]@row)

Where sheet A Project # is the cross sheet reference to the entire Project # column in sheet A. Let me know if you need help setting up a cross sheet reference. I think you almost had this yesterday.

If sheet A looks like this:

Sheet B would look like this:

Note - You can convert the Project #s from sheet A formula to a column formula so it will just keep working. However, the row numbers will only be added when rows are added to sheet B (not when they are added to sheet A) so you will need to make sure you add some spare rows.

Restrict the values returned based on the dropdown list

So far, we have done nothing about the Type column. I wanted to explain INDEX first. Now we have that working we can change the first part of the index. Rather than selecting the entire column as a range, we can use a COLLECT function to just COLLECT the values that meet a criteria.

That formula would look something like this:

=INDEX(COLLECT({sheet A Project #}, {sheet A Type column}, ="New Project"), [Row Number]@row)

Where sheet A Type column is a cross sheet reference to the Type column in sheet A.

The COLLECT function is collecting values from sheet A Project # where Sheet A Type column is equal to "New Project".

Now sheet B looks like this:

Remove the error messages

From row 4 onwards this does not look good. There is no data to display here and an error messages appears instead. It looks ugly and if you intend to do some MATCHing on this list later, any errors here will prevent those formulas from returning any matches even those that do exist. So we need to get rid of it.

We can do that by wrapping the formula in an IFERROR and returning "" (nothing) instead of the error message:

=IFERROR(INDEX(COLLECT({sheet A Project #}, {sheet A Type column}, ="New Project"), [Row Number]@row), "")

I recommend making that formula a column formula (and adding a lot of blank rows).

Let me know how you get on.

JayL

@KPH

thank you!!!

You've given me a lot to think about and work through. I'll post my solution when I have it.