M
mining north
I hit a problem in a 3 page spreadsheet used to calculate material movement:
The first page lists material to be hauled, with a name, volume, and
distance to a central point.
The second page lists places to fill with a name, volume, and distance to a
central point.
The third page works out a movement sequence based on equipment available.
The sequence is entered by entering the name of material to be hauled, the
name of the place to fill, and a maximum amount to haul at that point in the
sequence. There are 64 columns tied up in determining if there is enough
equipment to haul the desired amount, how many hours will be required, then
calculating how much will actually be hauled. The first row is
straightforward. On the second and subsequent rows I need to allow for the
fact that a part of each 'block' may have been hauled or filled previously in
the sequence. So, I start with a VLOOKUP comparing the designated name to
the first page for material originally available, then I use a SUMIF to
calculate how much has already been hauled under that name. When I subtract
the SUMIF from the VLOOKUP I get a circular reference error. I am handling
this by Iterating at the moment (the numbers don't change for different
numbers of iterations), but ultimately I will be sharing the workbook and
don't like to have 'tricks' that can cause problems for errors.
Any suggestions?
The first page lists material to be hauled, with a name, volume, and
distance to a central point.
The second page lists places to fill with a name, volume, and distance to a
central point.
The third page works out a movement sequence based on equipment available.
The sequence is entered by entering the name of material to be hauled, the
name of the place to fill, and a maximum amount to haul at that point in the
sequence. There are 64 columns tied up in determining if there is enough
equipment to haul the desired amount, how many hours will be required, then
calculating how much will actually be hauled. The first row is
straightforward. On the second and subsequent rows I need to allow for the
fact that a part of each 'block' may have been hauled or filled previously in
the sequence. So, I start with a VLOOKUP comparing the designated name to
the first page for material originally available, then I use a SUMIF to
calculate how much has already been hauled under that name. When I subtract
the SUMIF from the VLOOKUP I get a circular reference error. I am handling
this by Iterating at the moment (the numbers don't change for different
numbers of iterations), but ultimately I will be sharing the workbook and
don't like to have 'tricks' that can cause problems for errors.
Any suggestions?