Circular References

[Koda925]

Thanks for reading


I'm trying to make a circular reference work for me. I'm creating a unit cost sheet for construction estimating. In column D, I have the total number of units. In column F, G, and H, I have low, mid, and high unit cost prices, respectively. My goal here would to be track down my unit costs, plug those numbers in, and calculate my low, mid, and high totals in columns I, J and K. To do this, in cell I5, my low total cost, I used: =$F5*D5. This calculates my total units multiplied by the unit cost.

What I would like this function to do is if I am not able to get a unit price, but I get a bid for a total cost and I know the total number of units, To have cell F5, a unit cost column, to equal: $I5/D5.

When originally trying this formula, I got the circular reference notification so I went into my excel options and checked the box "enable iterative calculation"

It looks like it works, however, I have one complaint.

If I enter the unit cost and it calculates the total, I cannot go back and change the total and have it calculate the new unit cost. Is there any way I get can the formulas to update based on the most recent data entered? I have attached a screen shot for clarification.

Thanks!


spread sheet.jpg

[MrShorty]

On edit: It looks like you figured it out. (If you used the paper clip icon, it doesn't work (hasn't for a long time). In order to attach a file, you need to click on post reply or go advanced to bring up the main post editing page. On this page is a "manage attachments" link that will bring up the site's file uploader. Note that an actual Excel file is usually preferred over a screen shot.)

A cell can contain either a formula or a value, it cannot contain both. What I do in a situation like this is separate my input from my output. Designate 2 or 3 cells as inputs, where I can input the desired input value and some way of designating which input value it is. Then the output cells contain IF() functions that look at whatever I use for my input toggle and determine which formula to apply to the cell. To illustrate, in your case, I might designate I4 and F4 as my input cells (the cell left blank will be how I will tell which formula to calculate). If I am given total cost and want to calculate unit cost, I make sure to clear I4, and enter the total cost in F4. F5 contains a formula like =IF(ISBLANK(F4),$I4/D5,F4). I5 contains a similar =IF(ISBLANK(I4),$F4/D5,I4).

As one who likes to use helper cells, that's how I would do it. I know many users are adamantly opposed to helper cells (I sometimes wonder if they get special bonuses or something for using as few cells as possible). For these users, you must use a VBA macro (usually a change event procedure) in order to move the processing from the spreadsheet into VBA to overcome the "cell can contain either a formula or a value" limitation. Is this important enough to justify exploring using such a macro?

[Koda925]

I'm still learning excel, so it takes me a minute to play around and process what you're putting down. From what I'm getting, it doesn't sound like I'm able to do this without creating extra columns or rows to account for the different inputs and outputs. That makes sense. The problem here is that I already have over 300 rows for each trade and scope of work so in order to do that, my excel sheet would become very long. The idea for this work sheet is to have unit costs and/or totals already available so that when I go to estimate a job, I can get my total number of units, and come up with a quick cost. I would continue to update it based on changing prices. If I have a unit cost price for an item, but all the bids that I receive start coming in at a higher price, I would then want to come in and plug the new total to calculate my new unit cost in that area at that time. This will help with budgeting jobs very quickly for a customer while maintaining the most accurate and current prices for items. I hope I'm understanding what you're saying correctly.

I have played around with macros a bit. I found a few that allowed me to do quite a bit. It was pretty fun. I was able to edit a java script macro for google sheets to allow multiple users to edit one sheet. Anytime they edited the notes, it would put a date stamp and their email address in two columns. If they came back in and edited the notes again, it would update the email address of the user and the new date. It was pretty cool figuring that out. Of course, I just found the macro and played around with it to suit my needs. Don't think I could create one.

I would hate to trouble someone in making a macro for this, but if you know of any floating around that would be great!

[MrShorty]

You are using 300 rows out of 1 million (you don't say how many columns out of the 16000 you are using). I guess you have to decide when a few additional cells in all of those is too many.

As this is a common question, there are multiple examples of macros that do this. Found by searching "excelforum mirror cells change event":
https://www.excelforum.com/excel-pro...nt-sheets.html
https://www.excelforum.com/excel-gen...ror-cells.html

See what you learn from those.

[Koda925]

Thank you, MrShorty. I understand I am no where near the capabilities of excel and the more I learn, the more fascinated I become. Thank you for pointing out what I would even search to find a macro for something like that. I will play around with a few and also try your formulas as well. Thank you!