Index Collect Based on Another Cell
I have a drill down sheet in which I need to collect the plan values from the master.
Master
Drill
The Drill sheet contains SRC ID, Month & Year this can pinpoint a single value in the Master sheet.
If I create a range containing only the Jan column it works:
=INDEX(COLLECT({Master Range Jan Column}, {Master Range SRC ID}, [SRC ID]@row, {Master Range Plan Column}, "Plan"), 1)
However, this requires me to change the formula for each month, I tried creating a range which included all month columns in the range, at first hardcoding to look and index 10 for October but I couldn't get it to work, it wouldn't even work with Index 1:
=INDEX(COLLECT({{Master Range Months}}, {Master Range SRC ID}, [SRC ID]@row, {Master Range Plan Column}, "Plan"), 1,1)
Error is #Incorrect Argument Set
What am I doing wrong? Ideally, I would want to use a column formula and smartsheet workout the Month column automatically (the year is the easier one).
Thanks in advance.
Answers
-
This was the best I could come up with, if anyone could improve it that would be great.
=IF(Month@row = "Jan",
INDEX(COLLECT({MasterRange Jan Column}, {MasterRange SRC ID}, [SRC ID]@row, {MasterRange Plan Column}, "Plan", {MasterRange Year Column}, Year@row), 1),
IF(Month@row = "Feb",
INDEX(COLLECT({MasterRange Feb Column}, {MasterRange SRC ID}, [SRC ID]@row, {MasterRange Plan Column}, "Plan", {MasterRange Year Column}, Year@row), 1),
IF(Month@row = "Mar",
INDEX(COLLECT({MasterRange Mar Column}, {MasterRange SRC ID}, [SRC ID]@row, {MasterRange Plan Column}, "Plan", {MasterRange Year Column}, Year@row), 1),
IF(Month@row = "Apr",
INDEX(COLLECT({MasterRange Apr Column}, {MasterRange SRC ID}, [SRC ID]@row, {MasterRange Plan Column}, "Plan", {MasterRange Year Column}, Year@row), 1),
IF(Month@row = "May",
INDEX(COLLECT({MasterRange May Column}, {MasterRange SRC ID}, [SRC ID]@row, {MasterRange Plan Column}, "Plan", {MasterRange Year Column}, Year@row), 1),
IF(Month@row = "Jun",
INDEX(COLLECT({MasterRange Jun Column}, {MasterRange SRC ID}, [SRC ID]@row, {MasterRange Plan Column}, "Plan", {MasterRange Year Column}, Year@row), 1),
IF(Month@row = "Jul",
INDEX(COLLECT({MasterRange Jul Column}, {MasterRange SRC ID}, [SRC ID]@row, {MasterRange Plan Column}, "Plan", {MasterRange Year Column}, Year@row), 1),
IF(Month@row = "Aug",
INDEX(COLLECT({MasterRange Aug Column}, {MasterRange SRC ID}, [SRC ID]@row, {MasterRange Plan Column}, "Plan", {MasterRange Year Column}, Year@row), 1),
IF(Month@row = "Sep",
INDEX(COLLECT({MasterRange Sep Column}, {MasterRange SRC ID}, [SRC ID]@row, {MasterRange Plan Column}, "Plan", {MasterRange Year Column}, Year@row), 1),
IF(Month@row = "Oct",
INDEX(COLLECT({MasterRange Oct Column}, {MasterRange SRC ID}, [SRC ID]@row, {MasterRange Plan Column}, "Plan", {MasterRange Year Column}, Year@row), 1),
IF(Month@row = "Nov",
INDEX(COLLECT({MasterRange Nov Column}, {MasterRange SRC ID}, [SRC ID]@row, {MasterRange Plan Column}, "Plan", {MasterRange Year Column}, Year@row), 1),
IF(Month@row = "Dec",
INDEX(COLLECT({MasterRange Dec Column}, {MasterRange SRC ID}, [SRC ID]@row, {MasterRange Plan Column}, "Plan", {MasterRange Year Column}, Year@row), 1),
"Invalid Month"
))))))))))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!