CODEWITHSUNDEEP
excelexcel-formula
Rob Jr.
Rob Jr.

Reputation: 1

Works perfect but cell does't update

This is the formula I'm using in Excel sheet.It works fine but doesn't update sometimes and sometimes does what can be the reason

=IF(F3=1,SUM(INDIRECT(ADDRESS(CELL("row")-12,6))),IF(F3=2,SUM(INDIRECT(ADDRESS(CELL("row")-12,6)):INDIRECT(ADDRESS(CELL("row")-12,6+1))),SUM(INDIRECT(ADDRESS(CELL("row")-12,6)):INDIRECT(ADDRESS(CELL("row")-12,6+2)))))

Upvotes: 0

Views: 37

Answers (2)

Scott Craner
Scott Craner

Reputation: 152450

You can get away from all the volatile functions with INDEX:

=SUM(INDEX(F:G,ROW()-12,IF(F3=1,1,0))

Upvotes: 1

Luuk
Luuk

Reputation: 14899

The formula:

=IF(F3=1, 
   SUM(INDIRECT(ADDRESS(CELL("row")-12,6))), 
   IF(F3=2, 
     SUM(INDIRECT(ADDRESS(CELL("row")-12,6)):INDIRECT(ADDRESS(CELL("row")-12,6+1))), 
     SUM(INDIRECT(ADDRESS(CELL("row")-12,6)):INDIRECT(ADDRESS(CELL("row")-12,6+2)))
     )
   )

can be written simpler as:

SUM(INDIRECT(ADDRESS(CELL("row")-12,6 + IF(F3<=2,F3-1,2)))))

But this does not solve the problem.... 😉

Upvotes: 0

Related Questions