Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm using Excel 2010 and I have two worksheets. Sheet A has a unique identifier column and other columns with values. Sheet B has a list of identifiers and other fields for which I'm using a VLOOKUP formula to populate from the values in Sheet A.

Ideally, I would like to edit the values in place on Sheet B and have them update on Sheet A, but this doesn't seem possible - when I try it I just overwrite my =VLOOKUP formula with raw data. Am I correct that there is no way to do this?

Alternatively, is there a trick to click through or somehow automatically jump from the cell containing the VLOOKUP formula in Sheet B to the original cell in Sheet A?

My overall aim is to hold all raw values in Sheet A and only reference these from Sheet B without having to manually search Sheet A in order to update data.

share|improve this question

2 Answers 2

up vote 0 down vote accepted

To implement your alternative request, you can use the HYPERLINK function in conjunction with VLOOKUP

Assuming Sheet A contains ID's in Column A and values in column B, replace VLOOKUP with

=HYPERLINK("#'Sheet A'!B"&MATCH(A2,'Sheet A'!$A:$A,0),VLOOKUP(A2,'Sheet A'!$A:$B,2,0))

This will not only display the VLOOKUP result as before, but hyperlink to the found value in Sheet A when the cell is clicked.

share|improve this answer

The main issue you face now is circular referencing.

VLOOKUP isn't an A = B relationship but more like an if A then B relationship (where A and B are arrays of values like in your case). In other words, it is one directional and A is not dependent on B. From what I see, I don't think Excel has a direct way of handling a circular reference purely using the user interface, as the formula can't be stored in the backend (unless you use a macro).

One way is to have relative values in both A and B and a third sheet (or many extra columns) to do some conditional cells, e.g. Sheet A holds relative value (that could be a new updated value in B or an original value in C), Sheet C holds original values and Sheet B holds cells to reflect values AND update values.

In Sheet B, create a new column for every referenced column. This will be the update values column. In Sheet C, store your original values. In Sheet A, use a conditional that says "if update values field in Sheet B has a value, use it, else, refer to Sheet C". Sheet B's reflect values column will still reference from Sheet A so it will be dynamically change if the update column has new values. Note that this isn't really solving the circular reference problem but just going round it.

However, if you need Sheet A to be fixed values and not relative values, you will either have to copy and paste as value or use something like:

ActiveSheet.Range("A1:D1000").Value = ActiveSheet.Range("A1:D1000").Value

in VB to convert relative values (with formulas) to fixed values.

share|improve this answer
    
This is informative and confirms what I suspected. I accepted the other answer as it is the solution I have actually gone with. –  gmaclachlan May 2 '12 at 8:48
    
no worries. glad to help. –  Reuben L. May 2 '12 at 12:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.