Formulas causing circular reference

[jm263]

Hello,

I am trying to make this worksheet to help me with scheduling at work. I have it set up that if you put in a percentage in column B it will calculate the hours in column C and have a running total of the hours and percentage in D&E2. Is there any way to set it up that I can put in hours in column C to get the percentages in column B in addition to the way it is set up now without creating a circular reference? I would like to put the percentages in column B to get the hours in column C and then adjust the hours in column C which in turn would change the percentages. I have tried so many formulas in both columns but keep getting a circular reference error.

Thank you,

John

Attachment 313465

[6StringJazzer]

I would like to put the percentages in column B to get the hours in column C and then adjust the hours in column C which in turn would change the percentages.

What you said you want here is the very definition of a circular reference. You can't do that without a circular reference, and to complicate matters you can't have a formula and a constant in a cell at the same time. That is, if you overtype a formula in column C to force a change in hours, that formula is gone forever.

The only way to do this without creating a circular reference is to use VBA code to detect a change in either B or C (Worksheet_Change) and then recalculate the corresponding value in the other column.

See attached.

[jm263]

Hi Jeff,

Thank you for the response and help. When I try to enter figures, a prompt pops up stating
Run-time error '28': Out of stack space with the options to end or debug. Am I doing something wrong or
do I have to remove the existing formulas in the sheet?

Thanks again,

John

[6StringJazzer]

I don't get that problem but I think I know what's wrong. Try adding these lines to the code.

Please Login or Register  to view this content.

[jm263]

Hello,
That worked perfectly. You have saved me hundreds of work hours over the course of a year at my job. If you could help with one more thing with this worksheet, it will be complete. When I was just using formulas, I could change the number in the BUDGETED HOURS or FRONT END boxes and it would change the ALLOTTED HOURS TO SCHEDULE column as long as I had the PERCENTAGE of AVAILABLE HOURS filled in. Is there a way that if I could change the BUDGETED HOURS and/or FRONT END numbers and the two columns would change automatically?

Thank you so much,
John

[6StringJazzer]

It is possible but I need you to think a little about how you want this to work. Let's say I have 100 budgeted hours. On one line I have 10% and 10 hours. Now suppose you change Budgeted Hours to 200. Do you want to change the percentage to 5% and leave the 10 hours alone, or leave 10% alone and change the hours to 20? Or does it depend on which number I entered manually?

[jm263]

Hi Jeff,
Ideally I would like both the percentage and hours in the columns to change automatically when I alter the budgeted hours or front end hours

Thanks,

John

[jm263]

Hi,

I'll repost that. It didn't make sense.

John

[jm263]

Hi,

I would like the hours to change based off of the percentages when I alter the budgeted or front end numbers.

John

[jm263]

Hi,
Would the AVAILABLE HOURS TO SCHEDULE cell be the target cell since changing the BUDGETED HOURS or FRONT END hours is giving the number to be multiplied by the PERCENT OF AVAILABLE HOURS cells?

[6StringJazzer]

What do you mean by "target cell"? You mean Target in the VBA code? No. It has to be the cell that is actually changed the by the user. Cells with formulas that result in a different value do not trigger a Worksheet_Change event.

[jm263]

Hi,

I was just trying to figure out how to write the code. I'm not having any luck.

[6StringJazzer]

Here is a solution that takes a slightly different approach. The sheet starts out with the percentages as values and the hours as formulas. If you enter a percentage, the hours will be calculated automatically by the formula. If you type in the hours, then the percentage is calculated and updated, and the value that you typed in for hours is then replaced by the formula, giving the same result as what you typed in.

This has the advantage of having all the hours update from the formulas if A2 or B2 is changed.

[jm263]

Jeff,

This is perfect! I can not thank you enough.

John

[6StringJazzer]

You're welcome! Thanks for the rep, and thanks for marking your thread Solved!