How to update my sheet functions when I plug in new data

[GN0001]

Hello team,

I have a workbook that has functions such sum if, Sum Ifs and count ifs.

Each week I plug in the new data, sometimes the functions doesn't pick up the new values, what should I do to refresh the functions to pick up the new values?

Regards,

GN

 

[Faseeh]

Hi GGGGG,


You can do that by incorporating an OFFSET() function within these functions. For example if your data in present in Col A & B, (A1, B1 Downwards, these formula will automatically expand your ranges:

[pre]

For Sum         =SUM(OFFSET(B1,0,0,COUNTA(B:B),1))
For SumIf       =SUMIF(A1:OFFSET(A1,0,0,COUNTA(A:A),1),"A1",OFFSET(B1,0,0,COUNTA(B:B),1))
For Countif     =COUNTIFS(A1:OFFSET(A1,0,0,COUNTA(A:A),1),"A1",...)

[/pre]
..a better way to do this is to define OFFSET(A1,0,0,COUNTA(A:A),1) as a named range and then use it in formulas.


Regards,

Faseeh

 

[NARAYANK991]

Hi ,


Try doing a CTRL ALT F9 , which forces a global recalculation. If things still don't work out , check whether you have any formulae which refer to closed workbooks. These may work when the workbooks are open , but may not when they are closed.


Narayan