Return values from another sheet based on specific conditions
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
Answers
-
Hi @Mily C
The easiest way is to use the row report.
I have a helper column, Year, to group by the Year on the source sheet.
If you want to get the value in a sheet, I created a target sheet with a pre-created hierarchy like this;
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!