0

I have a worksheet that compiles daily generated data into a monthly overview. A new sheet is created each day, with the latest sales data. Within my monthly overview, I pull the data using this formula (in cell K13 for this example):

=IFERROR(VLOOKUP($D13, INDIRECT("'" & K$1 & "'!" & "D:E"), 2, 0), "")

where
D13 is the product to lookup;
K1 is the date that I'm pulling from;
D:E is the range of data to search from.

Notes:

-Using the indirect function to match each sheet reference search to the current date

-Using iferror to prevent N/A values on future dates.

The formula works perfectly for existing sheets, but whenever a new sheet is created, whether manually or automatically, the formula doesn't update with the result. Once I cut and paste the formula, it updates with the correct result.

I've tried changing the calculation settings within the sheet to auto calc every minute or hour, but that doesn't work. The only work around I've found is to manually cut and paste the equations.

tehhowch
  • 9,645
  • 4
  • 24
  • 42
cgm990
  • 36
  • 4
  • To clarify: You've pre-created the formula, and then later create the sheet that the formula is already referencing. Creating the sheet is not enough to make Google Sheets re-evaluate the formula, so you have to re-enter the formula. Does this still happen if you remove the `IFERROR` wrapper? – tehhowch Mar 15 '18 at 19:05
  • @tehhowch yeah that's essentially what I'm doing. Still happens without IFERROR, I included that just to visually clean up the sheet. – cgm990 Mar 15 '18 at 20:41
  • @I'-'I I just determined it was from INDIRECT as well. I'm not familiar with setFormula() so I'll will have to do some research on how to set it up. Any suggestions? My current workaround is to create all the sheets ahead of time, but that is time consuming. Thanks to both of you for the help! – cgm990 Mar 15 '18 at 20:44
  • @I'-'I OP is not doing this programmatically, so `setFormula()` isn't on the table (yet). @OP: The formula is simple enough that you should be able to do a pull-down operation from one that is existing and valid. A script (possibly standalone) could easily handle the programmatic creation of the monthly workbook. – tehhowch Mar 15 '18 at 20:47
  • @tehhowch could you clarify "pull-down operation from one that is existing and valid"? I can work my way around most spreadsheet operations but I'll be the first to admit I am quite new to script functions and that end of programming. – cgm990 Mar 15 '18 at 21:01
  • Pulldown, autofill, etc. In Excel, this would be using `Ctrl+D` or `Ctrl+R` to fill across a range, or selecting a (range of) cell and using the black cross in the lower-right corner to drag to new cells, setting their formulas to be extensions of the originally selected one. – tehhowch Mar 15 '18 at 21:04
  • @tehhowch Oh I follow. I've tried that before too with no success. It's strange behaviour, because in Google Sheets it will preview the result/value when editing the formula, but still won't update with the new result – cgm990 Mar 15 '18 at 21:13
  • @I'-'I I've also tried something similar to **rand()**, using **now()** in the formula; but same result (no update). I think you are correct in that the problem is with **Indirect()** – cgm990 Mar 15 '18 at 21:16
  • I think the problem is actually with referencing non-existent sheets, and finding a way to update the reference when the sheet is created. – cgm990 Mar 15 '18 at 21:19
  • 1
    @I'-'I Yeah I did. It's slightly better, editing any cell doesn't change the result, but if I cut and paste one cell in the column it updates (better than having to cut and paste the whole column). I'll mess around a little more with it. I'm going to try changing the calculation settings to calculate every minute instead of 'on change'. – cgm990 Mar 15 '18 at 21:30
  • @I'-'I I think it works! With the auto-calculate it should make it so I don't need to edit a cell. I'll add a filter in to get rid of the `rand()` values that appear. Thanks so much! – cgm990 Mar 15 '18 at 21:35
  • @I'-'I Just curious, why wouldn't you recommend this solution? I can see `rand()` can be unpredictable and maybe make the calculation more memory intensive? – cgm990 Mar 15 '18 at 21:45

0 Answers0