Can't figure out why formula is not working in my budget plan

jnaumann

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.

Genevieve P.

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:

Formula combinations for cross sheet references | Smartsheet Learning Center

https://help.smartsheet.com/articles/2482647-cross-sheet-formula-combinations

There are countless ways to combine functions to analyze your data and make it more useful.

Cheers,

Genevieve

Genevieve P.

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:

Formula combinations for cross sheet references | Smartsheet Learning Center

https://help.smartsheet.com/articles/2482647-cross-sheet-formula-combinations

There are countless ways to combine functions to analyze your data and make it more useful.

Cheers,

Genevieve

jnaumann

Index(Match worked much better. Thanks!