In Excel 2007 does the data from a formula referenced in another cell get used, or the formula itself?

30
2014-03
  • PenguinCoder

    Simple question and more than likely a simple answer.

    I have a workbook with a few worksheets, one of which is a 'data' worksheet. This information is utilized on another worksheet 'main' to fill in cell data of a few columns (using a vlookup). On the main worksheet, another column references the vlookup column in a formula to populate itself with information.

    In the column with the formula on main sheet; by referencing the cell with the vlookup in a formula (not the vlookup itself, but the cell/#) will the formula cell use the value from that cell or does it have to traverse the vlookup function back to the data worksheet to get the value it specifies, to be used in the formula?

    Should I just specify the vlookup to 'data' as part of the formula in the formula column/cell instead of the cell reference to the OTHER column? Which method is more efficient?

    • Answers
  • CharlieRB

    It will show the value from the cell you reference. Personally, I have simply referenced a cell with the VLOOKUP in order to echo the value needed rather than add a duplicate look-up.

    Keep in mind, for every VLOOKUP you have in a sheet/workbook it takes time to process. If the look-up range is small, it probably doesn't take a lot of time. But as the data range and workbook grows in complexity it may slow down.


    • Related Question

    vlookup - Edit referenced cells in master worksheet from derived worksheet in Excel
  • gmaclachlan

    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.


    • Related Answers
  • chris neilsen

    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.

  • Reuben L.

    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.