Index Collect
Hi everyone.
I need to build a sheet based on an approval matrix per function/dep code and differents amounts.
I figured out how to pull data from a cell using Index Match formula to bring the Approver on my target sheet based on the cell "Function/dep code" but now I need to bring the approver based on the amount input in column PO amount. Depending on the amount it would need approval from different people.
I tried using index collect but I kindly ask for help as I do not know how to build it
thanks!
Answers
-
INDEX/COLLECT syntax is actually really straight forward.
Build the COLLECT first.
COLLECT({Range To Pull From}, {1st Criteria Range}, 1st Criteria, {2nd Criteria Range}, 2nd Criteria, {3rd Criteria Range}, 3rd Criteria)
You can keep adding range/criteria sets until you have everything covered.
Now that you have COLLECTed all of the matching rows, you want to INDEX that first one.
=INDEX(COLLECT(.........), 1)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
thanks, Paul, very helpful. although I did struggle a little bit. still think that i need to improve the way i figured this out, (but at least it is working)
here's what I did
I made that work by (i) having an approval matrix sheet, in one column the department, another column for the approval level, eg: less than 100K USD, over 250K USD, then a 3rd column with the name (email) of the approver.
on my target sheet, I added a helper column referencing the amount column with a simple IF formula for the amount, eg: :IF(amount<100000,"less than 100K USD" ....and added the other criteria I needed)...
any suggestions are welcome.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!