Results 1 to 8 of 8

How to use a VLOOKUP to take formula rather than result of target cell

Threaded View

  1. #1
    Registered User
    Join Date
    02-10-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    14

    How to use a VLOOKUP to take formula rather than result of target cell

    Is there anyway I can use a VLOOKUP to take a formula from another cell rather than the result in the target cell. I don't want the actual formula displayed I want the formula to calculate from the location of the vlookup.

    The formula contains various INDIRECT references and is on a different sheet to the VLOOKUP (not sure if this complicates things).

    e.g.

    On Sheet1 I have a list of data and for each line of data a different formula is likely to apply. I have written the formulas on the Formula sheet and in this sheet they just return zero value or #DIV/0! error, which is fine and expected. If I copy and paste these formulas into Sheet1 they work great. So what I want to do is have a VLOOKUP on Sheet1 that tells the sheet which formula to use.


    example of formulas on formula sheet;

    =((INDIRECT(ADDRESS(ROW(),Formulas!$H$3,4))*INDIRECT(ADDRESS(ROW(),Formulas!$I$3,4))*INDIRECT(ADDRESS(ROW(),Formulas!$J$3,4)))/1000000000)*INDIRECT(ADDRESS(ROW(),Formulas!$K$3,4))
    =INDIRECT(ADDRESS(ROW(),Formulas!$H$6,4))/4.54609
    =INDIRECT(ADDRESS(ROW(),Formulas!$H$9,4))/INDIRECT(ADDRESS(ROW(),Formulas!$I$9,4))

    Whilst unlikely, I could potentially have over 100 different formulas which is why I have decided to try this method rather than an IF formula, but have only shown 3 formulas in this example as a basic illustration.

    Hope this makes sense, I also attach an example which might make it a little clearer.
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] IF(VLOOKUP) Formula That also Copies Cell Background Color to Target Cell
    By JRidge in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-09-2014, 06:38 AM
  2. [SOLVED] IF(VLOOKUP) Formula That also Copies Cell Background Color to Target Cell
    By JRidge in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-04-2014, 05:32 AM
  3. Replies: 5
    Last Post: 01-22-2013, 11:38 AM
  4. Formula in cell to result in a 1-5 score via Vlookup
    By jamietofs in forum Excel General
    Replies: 4
    Last Post: 12-12-2012, 12:58 AM
  5. Worksheet_CHange (ByVal Target as Range) when target is formula cell
    By coasterman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-09-2012, 07:00 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1