G
Guest
I know circular references aren't the best solution to most (all?) problems but I didn't see a way around in this case. I have a Cost analysis ss. There are multiple products with different production levels each day of the week. In addition to some fixed costs (constants) there are variable costs such as material and labor costs (user inputed numbers), and distribution, promotion and overhead costs. The problem is that distribution, promo and overhead and are expressed as a percentage of the total unit cost. (IE, Overhead may be estimated at 15% of unit cost - that is 15% of each units total cost goes to overhead). To make matters worse, there are two methods of distribution (on MWF and TuThSaSu). 4 days a week the distribution is outsourced at a fixed cost.
When I first set this up with circular references, things worked fine (although it took a while to get the bugs out). Since then I have added some very simple macros for data entry. All the macros do is prompt the user for input and assign those values to the correct cells. (btw: this isn't entirely neccessary, as there are not many users and using data validation is certainly an option - but I wanted to work with VBA and try to learn a bit.) Now whenever I open the spreadsheet I get the circular ref error message and #DIV/0 errors in my Profit calculations. Initially recopying the formulas containing the circ ref worked, but now not even that does
A few notes about the macros and formulas
There is a Worksheet_Open event that kicks off the data entry macros
I am using InputBox and Variant variables (so the user can exit the process with a 'Cancel'
The primary formula is
Total Cost=MaterialCost+LaborCost+(Overhead%+Promo%+Dist%)*TotalCos
Dist% = IF(DistCost="fixed rate",FixRate/SUM(FriProd)/TotalCost,DistCost%
Where
MaterialCost, LaborCost, Overhead%, Promo% are all hardcoded number
DistCost is a cell that contains either "fixed rate" or a percentage (which I call DistCost% above
SUM(FriProd) is the total number of units produced on Frida
When I trace precedents with the circ ref toolbar, I only get arrows with one set of cells. Does this mean the other cells are not generating errors? or does it stop when it finds the first one? (the cells in question are in the middle of the table.
Could using variant type variables be causing this? What is the difference b/w
dim x as varian
x = 1.
range("a1") =
and
dim x as singl
x = 1.
range("a1") =
Would it just be easier to add helper columns and try and do a work around (not sure if this is possible - haven't thought intensely about it yet)
Please help me. TI
When I first set this up with circular references, things worked fine (although it took a while to get the bugs out). Since then I have added some very simple macros for data entry. All the macros do is prompt the user for input and assign those values to the correct cells. (btw: this isn't entirely neccessary, as there are not many users and using data validation is certainly an option - but I wanted to work with VBA and try to learn a bit.) Now whenever I open the spreadsheet I get the circular ref error message and #DIV/0 errors in my Profit calculations. Initially recopying the formulas containing the circ ref worked, but now not even that does
A few notes about the macros and formulas
There is a Worksheet_Open event that kicks off the data entry macros
I am using InputBox and Variant variables (so the user can exit the process with a 'Cancel'
The primary formula is
Total Cost=MaterialCost+LaborCost+(Overhead%+Promo%+Dist%)*TotalCos
Dist% = IF(DistCost="fixed rate",FixRate/SUM(FriProd)/TotalCost,DistCost%
Where
MaterialCost, LaborCost, Overhead%, Promo% are all hardcoded number
DistCost is a cell that contains either "fixed rate" or a percentage (which I call DistCost% above
SUM(FriProd) is the total number of units produced on Frida
When I trace precedents with the circ ref toolbar, I only get arrows with one set of cells. Does this mean the other cells are not generating errors? or does it stop when it finds the first one? (the cells in question are in the middle of the table.
Could using variant type variables be causing this? What is the difference b/w
dim x as varian
x = 1.
range("a1") =
and
dim x as singl
x = 1.
range("a1") =
Would it just be easier to add helper columns and try and do a work around (not sure if this is possible - haven't thought intensely about it yet)
Please help me. TI
