I have the following formula on various sheets in an Excel workbook:
=SUM(SUMIFS(INDIRECT(CONCATENATE("'",REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),"")," HR","'!$e:$e")),INDIRECT(CONCATENATE("'",REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),"")," HR","'!$q:$q")),I$13,INDIRECT(CONCATENATE("'",REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),"")," HR","'!$j:$j")),{"שעון","בתפקיד","עבודה מהבית"}))
the formula takes the name of current sheet, concatenates the string " HR" to it and goes to a range of cells in sheet named as the concatenated string. It performs a few calculations on the data there and returns the value.
For some reason the cells in different sheets that contain the "same" volatile formula show me the values that would come up in another sheet. To see the right value I must enter only one of the formulas boxes in a relevant sheet, press enter and the evaluation turns to be correct.
I tried formula evaluation and theoretically it shows me the right calculation process on every sheet.
Any suggestions? Could it be some evaluation issue that is not related to formulas correctness?
You expect CELL to return information on the current cell when no reference is given, but it does not. CELL returns values based on the ACTIVE cell -- the cell that is active at the time of the calculation. Every time the cell recalculates, it will look at the active cell at that time, which may be on another sheet.
To see this in action, use the formula =CELL("address")&" "&TEXT(NOW(),"hh:mm ss") Since NOW is volatile, the cell will be recalculated every time you edit a cell. Edit any other cell, and you will see the address of that cell and the time it was calculated.
You will almost always want to include a reference value, eg CELL("filename",A1) To get the expected behavior, you'd just use a relative reference to the current cell.