If/Index/Collect Across two sheets
![clopez365](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
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?
Answers
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!