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.
Bookmarks