Reports pulling from formulas

06/28/19 Edited 12/09/19

I have reports calculating off multiple workflow sheets based on a column in the worksheet with formulas - the reports sometimes appear blank and when I go into the worksheet to see why, the formula column shows as blank and then reappears - almost like they only recalculate when looking at the sheet. the fomula is a nested vlookup within an if statement based off of a contact cell - is there anything that would cause this issue?

Popular Tags:

Comments

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    VLOOKUPS can be a little "sensitive". Sometimes they can get a little hinky and don't want to act right. Having said that...

     

    What is your actual VLOOKUP formula? Depending on your ranges, an INDEX/MATCH may be more efficient.

  • The formula is:

    =IFERROR(IF([Assigned To]@row <> "", (VLOOKUP([Assigned To]@row, {All Pod Detail Range 1}, 3, 0)), ""), "")

    The formula works like it should, the only issue is when reporting on it

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    How large is this range though?

     

    {All Pod Detail Range 1}

     

    I see there are at least 3 columns. Are there more than that? How many rows are there?

     

    I understand the formula functions as needed, but sometimes Smartsheet can easily get "formula heavy". That can cause all kinds of unintended consequences to include reports (which have regular issues anyway) not displaying properly and whatnot.

  • Ah gotcha - the range is 20 columns and ~500 rows

    is there any workaround thats best to decrease the formulas within a worksheet? I'd hate to keep creating reference sheets to supplement the reports

     

    Thanks for your help thus far!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Your range is covering approximately 10,000 cells right now. How many other columns do you reference within that range. Changing the VLOOKUP to an INDEX/MATCH may be the easiest way to go with improving performance.

  • Thanks I'll try to swtch the formula and see if it helps

Sign In or Register to comment.