If/Index/Collect Across two sheets

I'm getting an invalid value error but I'm not sure why with the following formula:

=IF([Journal Source]@row = "Supplier Invoice", INDEX(COLLECT({Forecast Sheet Row Number}, {Forecast Sheet Supplier}, Supplier@row, {Forecast Sheet Month}, [2023 Month]@row), 1))

Referenced Sheet:

Original Sheet (With Formula in the matching forecast column):

Here's my attempted logic with the formula:

If Journal source is Supplier invoice, then return the row number on the condition that the supplier and month match. 

Ideally I want the formula to lookin within the other sheet to find that match. But the invalid value is throwing me off

Q's:

  • Why do we have an invalid value and how can I fix it?
  • Am I using the right formula for my logic?


Tags:

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    @clopez365

    Is [2023 Month]@row from the formula on the "original sheet" or on the Forecast sheet?

    From the Formula error help page (linked in my signature):

    #INVALID VALUE error:

    Cause:

    The formula contains a number outside of the range that a function's argument expects. For example, this FIND formula that finds the character "H" in the text string "Hello" has a starting position of 100 characters...

    =FIND("H", "Hello", 100)

    ...Since the string "Hello" is 5 characters long, it isn't possible to have FIND start looking for the character "H" at 100 characters in. Only the numbers 1-5 can be accepted.

    Resolution:

    Make sure that all number arguments are within the range that the function expects.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!