Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Circular reference problem
Hi all, I've come up against a problem with referencing between two sheets in one workbook. The situation is as follows: On sheet1, I have a complete list of data. This is displayed in a different visual format on Sheet2. I have it set up so far so that, for example, if I enter / alter data in cell A1 on Sheet1, Sheet2 automatically enters / alters the data in a corresponding cell. However, on some occasions, I may want to alter the data in Sheet2 and have Sheet1 update the equivalent cell! This seems to cause problems! Does anyone have any suggestions as to a way round this?! Thanks in advance, Richard. -- R.Hocking ------------------------------------------------------------------------ R.Hocking's Profile: http://www.excelforum.com/member.php...o&userid=31092 View this thread: http://www.excelforum.com/showthread...hreadid=507618 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Circular reference problem
Suggestion #1: Don't do it that way. Only ever modify your data in one place or
eventually you'll shoot yourself in the foot. Particularly as your circular references become complex extending through multiple cells and you forget they're there even. Design your workbook in such a way as to not require this. Suggestion #2: If you're driven to do this, you can enable circular references with ToolsOptionsCalculation. Check the Iteration box. There's a very good reason not to do this though. Bill --------------------------------- R.Hocking wrote: Hi all, I've come up against a problem with referencing between two sheets in one workbook. The situation is as follows: On sheet1, I have a complete list of data. This is displayed in a different visual format on Sheet2. I have it set up so far so that, for example, if I enter / alter data in cell A1 on Sheet1, Sheet2 automatically enters / alters the data in a corresponding cell. However, on some occasions, I may want to alter the data in Sheet2 and have Sheet1 update the equivalent cell! This seems to cause problems! Does anyone have any suggestions as to a way round this?! Thanks in advance, Richard. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Circular reference problem
Although you can enable circular references, it's a dangerous thing to do.
Perhaps the better approach is to use VBA to sense when a cell is changed on one page and copy it's contents to the other. That may appear to be complicated for you though. If you're going to muck with VBA anyhow, only have one copy of your sheet and create two buttons on the sheet - one which will format the whole page one way, and the second which will reformat it the other. Then you never have to worry about the data getting out of synch between two pages. Just turn on the macro recorder, select the data and format it, then turn off the recorder. Do it again the second way. Now you have the two requisite macros. Good luck... Bill --------------------------------- R.Hocking wrote: Hi all, I've come up against a problem with referencing between two sheets in one workbook. The situation is as follows: On sheet1, I have a complete list of data. This is displayed in a different visual format on Sheet2. I have it set up so far so that, for example, if I enter / alter data in cell A1 on Sheet1, Sheet2 automatically enters / alters the data in a corresponding cell. However, on some occasions, I may want to alter the data in Sheet2 and have Sheet1 update the equivalent cell! This seems to cause problems! Does anyone have any suggestions as to a way round this?! Thanks in advance, Richard. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Circular reference problem
Thanks for your thoughts Bill - I know what you're saying about avoiding circular refs. I think in this case they would be ok, however. The scenario is that I have on one sheet a list of 810 individual items. These items are stored in 10 drawers of 9x9. Hence Sheet1 is the whole list, then I have a further 10 sheets to represent the layout and contents of each drawer. When an item is removed / altered I would normally enter this in the Sheet1 list, so all is fine. I just wondered if, on the odd occasion if I altered it in one of the other sheets, if I could get Excel to effectively do the reverse and update the Sheet1 ref! there are no real formulas, just an ='Sheet1'!$E2 type formula in each cell of sheets 2 to10. Pretty much a glorified filing system really! -- R.Hocking ------------------------------------------------------------------------ R.Hocking's Profile: http://www.excelforum.com/member.php...o&userid=31092 View this thread: http://www.excelforum.com/showthread...hreadid=507618 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Circular reference problem
Like I said, you can accomplish what you want with VBA if you're familiar with
that. You'd have to set it up to monitor when one cell is modified and then copy the modification to the other sheet. You'd need to use the Worksheet_Change event. Walkenbach describes it in his book "Excel 2003 Power Programming with VBA" if you have it, or if a local library has it. It's on page 624 in my particular edition of the book. Good luck... Bill ----------------------- R.Hocking wrote: Thanks for your thoughts Bill - I know what you're saying about avoiding circular refs. I think in this case they would be ok, however. The scenario is that I have on one sheet a list of 810 individual items. These items are stored in 10 drawers of 9x9. Hence Sheet1 is the whole list, then I have a further 10 sheets to represent the layout and contents of each drawer. When an item is removed / altered I would normally enter this in the Sheet1 list, so all is fine. I just wondered if, on the odd occasion if I altered it in one of the other sheets, if I could get Excel to effectively do the reverse and update the Sheet1 ref! there are no real formulas, just an ='Sheet1'!$E2 type formula in each cell of sheets 2 to10. Pretty much a glorified filing system really! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel too helpful! (problem with absolute reference) | Excel Worksheet Functions | |||
Cell reference problem | Excel Worksheet Functions | |||
Highest Value / Circular Reference Help | Excel Discussion (Misc queries) | |||
Highest Value / Circular Reference Help | Excel Worksheet Functions | |||
problem with cell reference in =sum(offset(cell reference,x,y,z,a)). Want cell ref to be variable. | Excel Worksheet Functions |