My question is: Is it possible to have a cell display a value of a referenced cell while also allowing you to modify that referenced cell by typing into the cell that's showing the other cells value. So that no matter where they change the ID, the tables ID is the one that will actually change.
IE:
Cell A1 shows value of cell B1,
Entering a value into A1 changes value of B1,
Cell A1 still shows value of B1,
antoher way of saying it:
Can A1's "displayed value" always be that of =B1, and always have values entered into A1 change the value of B1
Use Case:
First I'll describe what I currently have and the two types of references I use already., lets say that I have a product which has an ID#, that ID# is referenced throughout my workbook on many sheets and in many places. That ID may need to change during the documentation process. On sheet1 is a list of products and their ID# (and other info) Lets say that Product names are in Column A and the ID#s are in Column B
Other other sheets, you might type in a product in a cell (lets say D4) which auto populates (E4) with the associated ID#
On another sheet you may have a pre-built reference such as =A1 with =B1 next to it.
IE: (This is how the workbook currently works, simplified)
Legend = ["cellnumber"("value in cell")] or [A1(Product 1)]
Sheet 1: Setup as a table
[A1(Product Name)] [B1(ID#)]
[A2(Enclosure A)][B2(A2923)]
[A3(Device X)][B3(X323)]
Sheet 2:
[D4()][E4()] <---Starts Blank
[D4(Enclosure A)][E4()] <---A user enters "Enclosure A" in to D4 or uses a drop down menu to do so
[D4(Enclosure A)][E4(A2923)] <---"A2923" is auto populated into E4 from the table on sheet 1
Sheet 3:
[B5(=A2)][B6(=B2)] <-- And thus Displays Enclosure A in one cell and its ID below it A2923
[C5(=A3)][C6(=B3)]<-- And thus Displays Device X in one cell and its ID below it X323
There are many hundreds of products. So finding a particular one in the table is not 'easy' enough for some people with access to the workbook. They end up merely changing the value of the cell rather than finding and changing the value in the table of the referenced cell. Which results in the ID's being wrong all over the workbook. It would be better if every location that showed the ID# was not only a reference to B2 or B3 but also allowed you to change the value of B2 or B3 without destroying the reference.
