Can VLOOKUP return a formula??

[Iriemon]

Can a VLOOKUP return a formula rather than a value?

If not, is there any function that will return a formula?

I have a spreadsheet where depending on certain criteria, I need to use a
different formula. Unfortunately, I have 58 "rules" (criteria) I need to test
for to determine which formula to utilize. Right now I am performing
calculations for all 58 rules in a table on a separate worksheet and then
using a macro to bring in each part number, perform the calculations, do a
VLOOKUP and then return the correct result to the master worksheet. With
17,000 + rows, it is taking a bit of time to do.

Any suggestions on how to speed this up would be great!

Irie!

 

[NBVC]

Can a VLOOKUP return a formula rather than a value?

If not, is there any function that will return a formula?

I have a spreadsheet where depending on certain criteria, I need to us
a
different formula. Unfortunately, I have 58 "rules" (criteria) I nee
to test
for to determine which formula to utilize. Right now I am performing
calculations for all 58 rules in a table on a separate worksheet an
then
using a macro to bring in each part number, perform the calculations
do a
VLOOKUP and then return the correct result to the master worksheet
With
17,000 + rows, it is taking a bit of time to do.

Any suggestions on how to speed this up would be great!

Irie!

It might be easier to evaluate the criteria for all the formul
somewhere on the side.. and assign each result to a value you want t
look up and then use a Vlookup or similar to extract the proper resul
from the list of results..

--
NBV

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com

 

[JP Ronse]

Hi Irie,

If you don't mind to send me the file (data & maxro) in Excel 2003, I will
have a look if I can speed it up.

(e-mail address removed)

Wkr,

JP

 

[Luke M]

You could, with a little help from VB. Open up the VBE (Alt+F11), goto Insert
- Module, and paste this in:

'==========
Function EvalFormula(FormulaCell As String)
EvalFormula = Evaluate(FormulaCell)
End Function
'==========

Now, back in your workbook, you can setup your VLOOKUP table, and then your
formula will be something like:

=EvalFormula(VLOOKUP(formulaGoesHere))

EvalFormula will treat whatever is returned from the VLOOKUP as if it's an
actual formula.