I could use some help solving a circular reference. I have a 5 sheet file. The circular reference is between sheets 1 and 2............any help would be most appreciated.
Thanks
Chris
I could use some help solving a circular reference. I have a 5 sheet file. The circular reference is between sheets 1 and 2............any help would be most appreciated.
Thanks
Chris
Perhaps telling us what you are trying to do would help. At the moment unravelling your circular refs. is like trying to untangle spaghetti.
If we know the aim and what the basic dependencies are we may be able to figure out an alternative approach.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
Thanks for the response.
I have gone through the spreadsheet and for the life of me come up with where the circular reference is coming from.
What is driving me nuts is when I take a cell N44 ($234,922) (sheet ML Pro Forma) and then link to it by putting =N44 ML Pro Forma in another cell the result is 0. So =N44 in a new cell always returns 0......yet I checked the formatting and for the life of me can't figure it out. Hopefully, this makes sense..................thanks
Another thing to consider is that the actual problem is unlikely to be where excel says it is. Cic Refs cause all sorts of strange behaviour in a WB
My approach is often to delete where excel says the problem is - if that fixes it, then you know where to change stuff. In the likely event that that is not the problem, excel will point you to the next "problem cell (if it is an entire column/row of the same formula, delete them all, and see where it points to next. Continue doing this until the error goes away, andthen start back-tracking to see if you can spot the error
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
What you are trelling us makes perfect sense - to you lol. We dont know your layout/purpose/function etc, it will make it a LOT easier if you can upload a sample of what you are working with (no sensitive data)
Here is the spreadsheet
And thats exactly what that 1 is, wow. I hate trying to wade through a complex workbook like
That's one of the reasons I won't download a file bigger than 50kb.
Files make sense to the person who created it but it may not make sense to me!
Hi Chris,
As FD has suggested the easiest way forward is to remove all the cells which are identified by the circular ref - if you are using XL2003 temporarily turn off the iterations and the circular refs are noted underneath the sheet tab names.
Once you've done that and there are no circ. refs then start creating your relationshios again. As soon as you hit a new circ. ref review what you are doing and see if there is not another way, perhaps by using a calculation again rather than referring to another cell which has that calculation.
If you still can't get rid of it then post back with just the first circ ref in place and tell us what you are trying to do. We'll then be in a better position to help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks