Forum Home
Press F1
Thread ID: 39349	2003-11-04 02:36:00	Super Tricky Excel Problem !!	simon1 (4829)	Press F1

Post ID	Timestamp	Content	User
189034	2003-11-04 02:36:00	I have a real tough problem which may not have a solution. I want to refer to a particular cell with two reference numbers. Bearing in mind they have to be totally independent of row and column references used in excel. Its complex to explain but i'll come back to that. Basically each cell will refer to another cell. with no calculations at all. 99% of the time it will be one cell referring to one other. This represents wires in the real world with upwards of 6000 connection points! That means around 3000 wires. So each cell will simply contain the address/location of the other cell which it will be wired to. So far so good, at the moment cell A will have the location/name of cell B written in it. And then I go to cell B and full it with the location/name of cell A. This is the current setup. However it is prone to human error. So my question is: "If I write in cell A the location of cell B, will excel automatically fill cell B with the location of cell A? And will it tell me if cell B already is connected to some other cell?" So as you can see its a bit hard. As you may figure when dealing with up to 6000 cells, cross checking becomes somewhat impossible. Heres an example of cell names that I have typed it out as a spreadsheet: 1/101.........1/110 2/101..........2/110 3/101..........3/110 1/111.........1/120 2/111..........2/120 3/111..........3/120 The above represents only 60 cells. In total it will go across from 1/ to 10/ and down from 101 to 700, and therefore 6000 cells in total. eg: cell 5/123 is connected to 7/457. Simply by witting "7/457" in the first cell should automatically write up "5/123" in the second. At the moment excel row & column refs are irrelevant/transparant And I would like to keep things that way. I have thought about re-naming the cells (eg changing 13B to 2/198) Even using macros but cannot find functions that would help. If this sounds like it might have a solution (or possibly a pre made routine/function ) Then I will be a very happy man. With fewer hairs turning grey! Thanks Simon	simon1 (4829)
189035	2003-11-04 02:45:00	Ummm. The old joke might apply here: City Slicker: How do I get to Dublin from here? Yokel: If I wanted to go there, I wouldn't start from here. I think I'd write a programme. :D The term "linked list" comes to mind. If you are forced to use packaged software, a relational database, rather than a spreadsheet, might be a better bet.	Graham L (2)
189036	2003-11-04 03:50:00	Hi Simon1, I dont know a perfect answer for this and you may be better off posting this question in an Excel specific forum like Mr Excel (http://www.mrexcel.com). What you are talking about is depenancies and precedents. A dependancy is any cell that the current active cell refers to in order to make its calculation while a precedent is where the active cell is refered to by another cell. For example, say in cell C1 I have a formula of =A1 + B1, then cell C1 has a dependancy on cells A1 and B1. Using your goal I suppose you could call this the left side of the wire. The flipside (precedents) is the opposite direction where say cell D1 has =C1, so C1 is refered to by another cell and thefore the precedent for C1 is D1 (the right side of the wire). Excel has a specific menu with several tracing options and can show precedents and dependancies on the selected cell/s - see Tools|Formula Auditing. hth	parry (27)
1