Can't figure out why formula is not working in my budget plan
Creating a budget plan using a Smartsheet template. I initially deleted the code names because they weren't useful to my project. The formula search_value was linked to code name and produced an error message. I adjusted the search_value and column_num to match the correct linked data and had no issues in any other month except August. What is going on?
Last resort is I can hard code the data but really want to avoid that if possible to avoid human error.
Best Answer
-
Hi @jnaumann
Does your August column in the source sheet have any errors in it? Formulas will show an error if even one cell in the selected range has a formula error as well.
Check all the cells in the reference {Sheet - General Ledger (08 - August) Range 1} to see if there are any formula errors. If there are, use an IFERROR to get rid of it, and this should update your current formula.
Another thing to try is to use an INDEX(MATCH formula instead:
=INDEX({Column to return}, MATCH("Matching Value", {Column with value to match}, 0))
Or in your case:
=INDEX({August Column}, MATCH([Account Name]3, {Account Name Column}, 0))
Here's more information:
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Hi @jnaumann
Does your August column in the source sheet have any errors in it? Formulas will show an error if even one cell in the selected range has a formula error as well.
Check all the cells in the reference {Sheet - General Ledger (08 - August) Range 1} to see if there are any formula errors. If there are, use an IFERROR to get rid of it, and this should update your current formula.
Another thing to try is to use an INDEX(MATCH formula instead:
=INDEX({Column to return}, MATCH("Matching Value", {Column with value to match}, 0))
Or in your case:
=INDEX({August Column}, MATCH([Account Name]3, {Account Name Column}, 0))
Here's more information:
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Index(Match worked much better. Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.2K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 142 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!