| Home |
| Search |
| Today's Posts |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
circular reference where sumif and vlookup overlap
Glad to help.
-- John C "mining north" wrote: 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" wrote: 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! -- John C "mining north" wrote: 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" wrote: Thanks for the other choices, though to be fair, I never asked him to post to newsgroup. -- John C "Gord Dibben" wrote: 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 On Fri, 11 Jul 2008 13:51:01 -0700, John C <johnc@stateofdenial wrote: 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). |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Circular Reference - Why? | Excel Discussion (Misc queries) | |||
| circular reference using vlookup | Excel Worksheet Functions | |||
| Circular Reference | Excel Worksheet Functions | |||
| Circular Reference | Excel Discussion (Misc queries) | |||
| Ho do I do this without a circular reference? | Excel Discussion (Misc queries) | |||
![John C[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Threaded Mode
