Hi,
In the attached file in column H there is a calculation based on column G.
When I drag the formula in column G to the following rows, an error message appears -
what can be done??
I will note that in my complete file in column H there are more complex calculations based on column G...
Currently Column G conditionally sums values from Column H. In turn, Column H depends on the value in column G in cases where the second IF is evaluated.
What exactly do you want to see in columns G and H?
Column G should summarize the weekly hours for the current week.
In column H he calculates the total daily hours 100% -
checks if it is Friday and also 42 minus the cumulative hours is less than 7 = then calculate 42 - the cumulative hours.
The question is if there is a way to tell Excel not to get stuck with the circular reference
or if I will have to think of another solution in the calculations?
I'd say you need to find a different calculation. Perhaps the attached file helps you on your way. If not please clarify your intentions. You still haven't mentioned why you reference cells in column Q, 10 rows down from the current row. And what happens if that value in Q is not less then 7? Currently, the formula returns FALSE as there is no 'value_if_false' declared in the IF statement.
The formula in column H is less important to me, because in my file it is a very complex formula.
My problem is that I need to summarize in column G the hours from column H.
In the attached file I saw that you summarized the hours from column F.
Need to summarize from column H
But then there is a problem with circular reference
Any idea for a solution?
No! Sorry.
The strange thing is tha every time you refresh the formula the circular reference appears in a different row, if varies.
Another thing I don't get is why H35 adds upo 208 and if the H2:H32 is a named range it adds to 0
Many strange things happen in this file