circular reference where sumif and vlookup overlap

[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?

 

[John C]

This appears to be a very involved spreadsheet. I think I would need to look
more closely at your data structure. Can you post it? or email it? (with I
assume, dummy data).

 

[mining north]

It's fairly involved. How would I post it?

This appears to be a very involved spreadsheet. I think I would need to look
more closely at your data structure. Can you post it? or email it? (with I
assume, dummy data).

 

[John C]

There are several websites that allow free hosting of files. I have not used
any, but have seen other links in these help pages.

One such website that I have seen used a lot is http://www.mediafire.com

Or you could e-mail it to me at (e-mail address removed)

 

[Gord Dibben]

Please do not post a file to the newsgroup.

If you want to upload a copy of your workbook to one of the file hosting sites
we can download to see what could be preventing CF from working.

http://www.freefilehosting.net/
http://savefile.com/

When you have uploaded to that site, post the URL so we could download should we
choose.


Gord Dibben MS Excel MVP

 

[John C]

Thanks for the other choices, though to be fair, I never asked him to post to
newsgroup.

 

[mining north]

It's all new to me. The file is at:
http://www.freefilehosting.net/download/3jeg7
Any advice is welcome. I'm following through with Excel set on iteration
for the time being.

 

[John C]

Took me a little while longer to figure out than I planned, but I did figure
it out.

The cells that are creating the problems, initially, are the following cells:
AY14, BD14, BI14, BN14, and BS14.
In order to fix the circular reference error, I had to change up the
formulas in these cells just a tad, along with the the subsequent cells in
those columns.
The corrections below should do the trick. NOTE: I am giving 2 formulas for
each column, 1 for row 14, and 1 for row 15, the formulas in row 15 should be
edit-->fill-->down as needed.
AY14: =$T$6-SUM(Z$14:Z14)
AY15: =$T$6-SUM(Z$14:Z15)-BC14
BD14: =$T$7-SUM(AE$14:AE14)
BD15: =$T$7-SUM(AE$14:AE15)-BH14
BI14: =$V$4-SUM(AJ$14:AJ14)
BI15: =$V$4-SUM(AJ$14:AJ15)-BM14
BN14: =+$V$5-SUM(AO$14:AO14)
BN15: =+$V$5-SUM(AO$14:AO15)-BR14
BS14: =$V$6-SUM(AT$14:AT14)
BS15: =$V$6-SUM(AT$14:AT15)-BW14

The issue with the way you had it is that cells in row 14 were tabulating
information from rows 14 through 63, which fed into column BX which fed
information back into columns G & H.

This should also give an accurate representation of running balance of
available hours on the latest lines.

Hope this helps you!

 

[mining north]

That was the path. Summing the column is where I the logic fell apart, but
to pull it back on track I ended up with:
V14: =$T$6
V15: =V14-Z14-BC14
AY14: =V14-Z14
AY15: =V15-Z15
.... and the same across the fleets. For each type of equipment (495, EH500,
930E, etc) I needed to run out the hours by row.

You've saved me a concussion from banging my head against the wall!!

 

[John C]

Glad to help.
--
John C

That was the path. Summing the column is where I the logic fell apart, but
to pull it back on track I ended up with:
V14: =$T$6
V15: =V14-Z14-BC14
AY14: =V14-Z14
AY15: =V15-Z15
... and the same across the fleets. For each type of equipment (495, EH500,
930E, etc) I needed to run out the hours by row.

You've saved me a concussion from banging my head against the wall!!