Dynamically populate column values from one sheet to another with conditions

Laurie Mason

Hi all,

I am trying to populate a column of project codes from the Master Project List to a metric sheet. All the project codes are unique, but from what I have read, to keep it updating dynamically I have to have a Unique number (primary column). I was able to get this to work with this formula:

=IFERROR(INDEX(DISTINCT({Master Project List - Project Code}), [Primary Column]@row), "")

However, there are almost 800 project codes. I'm already having a problem with too many cross-Sheet references on the Metrics sheet. So, I only want the ones that are marked as Active. I tried the formula below, but can't get it to work.

=IF({Master Project List - Active} "Active", INDEX(DISTINCT({Master Project List - Project Code}), [Primary Column]@row), "")

Paul Newcome

You'll need a COLLECT function like so:

=IFERROR(INDEX(DISTINCT(COLLECT({Master Project List - Project Code}, {Master Project List - Active}, @cell = "Active")), [Primary Column]@row), "")

Is there a particular reason you are pulling into a metrics sheet as opposed to a report?

Paul Newcome

You'll need a COLLECT function like so:

=IFERROR(INDEX(DISTINCT(COLLECT({Master Project List - Project Code}, {Master Project List - Active}, @cell = "Active")), [Primary Column]@row), "")

Is there a particular reason you are pulling into a metrics sheet as opposed to a report?

Laurie Mason

Hi Paul,

Hmmm, that formula isn't working. it gives me an "Incorrect Argument" error.

I am not using a report because there are a lot of calculations and cross-cell references needed in the metrics sheet. It is only pulling the Project code from the Master Project List.

Paul Newcome

Double check the ranges selected for the cross sheet references. They should all be from the same sheet and you should select them all by clicking on the appropriate column header.

Laurie Mason

Laurie Mason

https://community.smartsheet.com/discussion/comment/362144#Comment_362144

Thanks Paul!! I played around a little longer and got it to work! Still not sure why it wasn't working before, but it is now! Again, Thank you!