How do I combine multiple INDEX Collect formulas in one Formular?
Hello, dear support team,
I have a question about combining several Index Collect functions.
The Goal is: I want to get the Payment Terms into the Cash Out section of the Cashflow.
As you can see in the picture below;
25% of Product #1 has to be paid in the calendar week 8. 50% of P#1 in CW9 and the remaining 25% in CW10. This information is Put in manually.
The formula that gets this information is as follows:
=IFERROR(INDEX(COLLECT({01 BoM #1 Value}, {01 BoM Cost Code}, [Nr. ]@row, {01 BoM #1 CW}, [PW 1]1, {01 BoM #1 Year}, [PW 1]2), 1), "")
it searches for the Ammount of Money by the criteria of Cost Code, Calender Week and Calender Year. But it can only get the information of on Payment.
You can see the result and the Formula in the pictures below:
Now my question is: how can I Create a Formula that will search for all three payment terms and will display only the correct Payment term with the corresponding calendar week and Year?
My try was:
=IFERROR(INDEX(COLLECT({01 BoM #1 Value}, {01 BoM Cost Code}, [Nr. ]34, {01 BoM #1 CW}, [PW 2]1, {01 BoM #1 Year}, [PW 2]2), ), INDEX(COLLECT({01 BoM #2 Value}, {01 BoM Cost Code}, [Nr. ]@row, {01 BoM #2 CW}, [PW 2]1, {01 BoM #2 Year}, [PW 2]2), ), "")
But it Displays #Unparseable
What would a correct combination look like?
Best Answer

Your closing parenthesis are off. Build it out one at a time like so:
=INDEX(COLLECT(1st one), 1)
=IFERROR(above formula, INDEX(COLLECT(2nd one), 1))
=IFERROR(above formula, INDEX(COLLECT(3rd one), 1))
=IFERROR(above formula, "")
Basically just keep wrapping around the outside until you have as many as you need.
=INDEX(COLLECT(1st one), 1)
=IFERROR(INDEX(COLLECT(1st one), 1), INDEX(COLLECT(2nd one), 1))
=IFERROR(IFERROR(INDEX(COLLECT(1st one), 1), INDEX(COLLECT(2nd one), 1)), INDEX(COLLECT(3rd one), 1))
=IFERROR(IFERROR(IFERROR(INDEX(COLLECT(1st one), 1), INDEX(COLLECT(2nd one), 1)), INDEX(COLLECT(3rd one), 1)), "")
Answers

Looks like your #Unparseable error may be due to this extra comma with no parameter to follow. Remove it completely or add a column number for the Index function.
Smartsheet Solutions Architect
www.adapture.com

Hello Mr. Ramzi,
thanks for the suggestion and help, but the new function results in "#incorrect argument set"
I tried three versions:
#1 without any comma in Row Number #34:
=IFERROR(INDEX(COLLECT({01 BoM #1 Value}, {01 BoM Cost Code}, [Nr. ]@row, {01 BoM #1 CW}, [PW 2]1, {01 BoM #1 Year}, [PW 2]2)), INDEX(COLLECT({01 BoM #2 Value}, {01 BoM Cost Code}, [Nr. ]@row, {01 BoM #2 CW}, [PW 2]1, {01 BoM #2 Year}, [PW 2]2)), "")
#2 with the Column number (36 and 40) of the asked amount:
=IFERROR(INDEX(COLLECT({01 BoM #1 Value}, {01 BoM Cost Code}, [Nr. ]@row, {01 BoM #1 CW}, [PW 2]1, {01 BoM #1 Year}, [PW 2]2), 36), INDEX(COLLECT({01 BoM #2 Value}, {01 BoM Cost Code}, [Nr. ]@row, {01 BoM #2 CW}, [PW 2]1, {01 BoM #2 Year}, [PW 2]2), 40), "")
#3 with an index function/number: i use 0 and 1  both resultet in "#incorrect argument set"
=IFERROR(INDEX(COLLECT({01 BoM #1 Value}, {01 BoM Cost Code}, [Nr. ]@row, {01 BoM #1 CW}, [PW 2]1, {01 BoM #1 Year}, [PW 2]2), 1), INDEX(COLLECT({01 BoM #2 Value}, {01 BoM Cost Code}, [Nr. ]@row, {01 BoM #2 CW}, [PW 2]1, {01 BoM #2 Year}, [PW 2]2), 1), "")
For any clarification, I will show below the Sheet (BoM) that the information has to be taken out of. In row 1 you can see the column numbers of the amount € that I have to get in the Cashflow Plan.
Do I need to include an AND OR function? or some sort of other differentiator?

You need to add another IFERROR to the front.
Right now your formula is saying to run the first INDEX/COLLECT. If there is an error with the first one, then run the second one. That leaves your final piece of , "" just hanging out there.
Try this:
=IFERROR(IFERROR(INDEX(COLLECT({01 BoM #1 Value}, {01 BoM Cost Code}, [Nr. ]@row, {01 BoM #1 CW}, [PW 2]1, {01 BoM #1 Year}, [PW 2]2), 1), INDEX(COLLECT({01 BoM #2 Value}, {01 BoM Cost Code}, [Nr. ]@row, {01 BoM #2 CW}, [PW 2]1, {01 BoM #2 Year}, [PW 2]2), 1)), "")

Hello Paul,
That was very helpful. It works now ( for the second Payment term)!
Now I have a new question. How do I add a third, fourth, fifth, and sixth payment term?
Given that I created Columns in the BoM for the next Payment terms.
I tried to add the IFERROR and a new line for the third payment term but it shows now "Incorrect Argument Set"
=IFERROR(IFERROR(IFERROR(INDEX(COLLECT({01 BoM #1 Value}, {01 BoM Cost Code}, $[Nr. ]@row, {01 BoM #1 CW}, [PW 3]$1, {01 BoM #1 Year}, [PW 3]$2), 1), INDEX(COLLECT({01 BoM #2 Value}, {01 BoM Cost Code}, $[Nr. ]@row, {01 BoM #2 CW}, [PW 3]$1, {01 BoM #2 Year}, [PW 3]$2), 1), INDEX(COLLECT({01 BoM #3 Value}, {01 BoM Cost Code}, $[Nr. ]@row, {01 BoM #3 CW}, [PW 3]$1, {01 BoM #3 Year}, [PW 3]$2), 1))), "")
Is this maybe getting too long?
Another side question: is there maybe another "easier" way to do this or is this the correct way?

Your closing parenthesis are off. Build it out one at a time like so:
=INDEX(COLLECT(1st one), 1)
=IFERROR(above formula, INDEX(COLLECT(2nd one), 1))
=IFERROR(above formula, INDEX(COLLECT(3rd one), 1))
=IFERROR(above formula, "")
Basically just keep wrapping around the outside until you have as many as you need.
=INDEX(COLLECT(1st one), 1)
=IFERROR(INDEX(COLLECT(1st one), 1), INDEX(COLLECT(2nd one), 1))
=IFERROR(IFERROR(INDEX(COLLECT(1st one), 1), INDEX(COLLECT(2nd one), 1)), INDEX(COLLECT(3rd one), 1))
=IFERROR(IFERROR(IFERROR(INDEX(COLLECT(1st one), 1), INDEX(COLLECT(2nd one), 1)), INDEX(COLLECT(3rd one), 1)), "")

Yes!
This works now for all Payment terms.
Thank you very much!

Happy to help. 👍️
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!