Return values from another sheet based on specific conditions

Mily C

Hi All,

Can anyone help me by return selected columns of data from another sheet?

I already use COUNTIFS formula to count the total project number in 2021 is 5, and is there any formula that can automatically generate the project code, requester, and project location from the raw data?

Here is the raw data

Not sure if there is a formula that can pop up all 5 projects in 2021 with selected cells, I also want to show projects from 2022 to 2024. So it's better if the formula can pop up all projects..😓

Thanks for the help!

Mily

jmyzk_cloudsmart_jp

Hi @Mily C

The easiest way is to use the row report.

https://app.smartsheet.com/b/publish?EQBCT=d405c17666514b199b5fb5848fbf96d9

I have a helper column, Year, to group by the Year on the source sheet.

https://app.smartsheet.com/b/publish?EQBCT=4ee7bd328a9a40dbb38daca7067a3a45

If you want to get the value in a sheet, I created a target sheet with a pre-created hierarchy like this;

https://app.smartsheet.com/b/publish?EQBCT=6ef28d7031fe4322823956a060ccaf8fZ

The cell history shows that the 2022 and 2023 groups are copied from the top 2021 groups. I later changed the 2021 to 2022, 2023, and so on.

On the Souce sheet, I have an ID column with the following formula;

=Year@row + "-" + MATCH([Row ID]@row, COLLECT([Row ID]:[Row ID], Year:Year, Year@row))

Similarly, on the target sheet, I have an ID column and other helper columns as follows;

[ID] =PARENT(Project@row) + "-" + Child@row
[is Parent] =IF(COUNT(CHILDREN()) > 0, 1)
[Project] =IF([is Parent]@row, "", IFERROR(INDEX(COLLECT({Source Sheet : Project}, {Source Sheet : ID}, ID@row), 1), ""))
[Requester] =IF([is Parent]@row, "", IFERROR(INDEX(COLLECT({Source Sheet : Requester}, {Source Sheet : ID}, ID@row), 1), ""))
[Location] =IF([is Parent]@row, "", IFERROR(INDEX(COLLECT({Source Sheet : Location}, {Source Sheet : ID}, ID@row), 1), ""))

Note that the [Project] column's formula is a cell formula, as I need to change the Year value from 2021 to the succeeding years in other parent groups.

This solution is not so elegant and involves a lot of manual work.
So, I recommend the row report version.