Reports pulling from formulas

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?

Find more posts tagged with

Formulas

Reports

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Issue with automation process reporting a date in a special column
In the Smartsheet sheet “CRS RO Master” we have programmed an automation called “Track Changes in dates, confirmation and LHG code” [PICTURE 1] This one should report a date of a change in the columns “CRS GO LIVE DATE” and “PMS Go LIVE DATE” in the column “. CRS Check date changed”. [PICTURE 2] The update of the “Go live…