Reports pulling from formulas

barry_CL_smartsheet

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?

Paul 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.

barry_CL_smartsheet

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 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.

barry_CL_smartsheet

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 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.

barry_CL_smartsheet

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