convert text to formula using VLookup
 

My question;
I've seen somewhere that you can convert a text string into a formula using
the INDIRECT Function.

What I am constructing is a maintenance Schedule where some items need to be
serviced on a weekly basis, others monthly, quarterly, bi-annually, and
yearly. (Those are the only five rotations I require at the moment.) Once
constructed the format will take on the appearance of a calendar with
conditional formatting to highlight the dates the service falls on.

Each of the rotations require (I think) different formulas, so what I want
to do is have a list of the five different formulas minus the "=" sign in a
lookup table, then have it concatenate in the relevant cell as a formula.

EG in D7 if C7 contains "Weekly" then I want it to lookup the formula table
in A1:B5, find "Weekly" in ColA and return the corresponding formula in ColB
appended with an "=" sign to create a Formula.

FYI the formulas are working on dates, ie, depending on whether the result
falls between a period start date and a period end date determines whether or
not the result is displayed.

Regards
Vicki

convert text to formula using VLookup
 

Just Confirm what is the value is present on B Column? Or provide some B
Column Values for examples.

--------------------
(Ms-Exl-Learner)
--------------------


"VickiMc" wrote:

My question;
I've seen somewhere that you can convert a text string into a formula using
the INDIRECT Function.

What I am constructing is a maintenance Schedule where some items need to be
serviced on a weekly basis, others monthly, quarterly, bi-annually, and
yearly. (Those are the only five rotations I require at the moment.) Once
constructed the format will take on the appearance of a calendar with
conditional formatting to highlight the dates the service falls on.

Each of the rotations require (I think) different formulas, so what I want
to do is have a list of the five different formulas minus the "=" sign in a
lookup table, then have it concatenate in the relevant cell as a formula.

EG in D7 if C7 contains "Weekly" then I want it to lookup the formula table
in A1:B5, find "Weekly" in ColA and return the corresponding formula in ColB
appended with an "=" sign to create a Formula.

FYI the formulas are working on dates, ie, depending on whether the result
falls between a period start date and a period end date determines whether or
not the result is displayed.

Regards
Vicki

convert text to formula using VLookup
 

You can't "build" a formula that way. Concatenated strings will always be
evaluated as a text string even though it might *look* like a formula

Instead of returning a "formula" try returning the *result* of the formula.

--
Biff
Microsoft Excel MVP


"VickiMc" wrote in message
...
My question;
I've seen somewhere that you can convert a text string into a formula
using
the INDIRECT Function.

What I am constructing is a maintenance Schedule where some items need to
be
serviced on a weekly basis, others monthly, quarterly, bi-annually, and
yearly. (Those are the only five rotations I require at the moment.) Once
constructed the format will take on the appearance of a calendar with
conditional formatting to highlight the dates the service falls on.

Each of the rotations require (I think) different formulas, so what I want
to do is have a list of the five different formulas minus the "=" sign in
a
lookup table, then have it concatenate in the relevant cell as a formula.

EG in D7 if C7 contains "Weekly" then I want it to lookup the formula
table
in A1:B5, find "Weekly" in ColA and return the corresponding formula in
ColB
appended with an "=" sign to create a Formula.

FYI the formulas are working on dates, ie, depending on whether the result
falls between a period start date and a period end date determines whether
or
not the result is displayed.

Regards
Vicki

convert text to formula using VLookup
 

The one formula I have devised for ColumnB so far is as below: please note
that it is purely indicative at this stage and might well not be that which I
end up with, anyway....

IF(B49="Quarterly",Sch_Comm_Date-$C49,Sch_Comm_Date+INDIRECT(B49))

The Quarterly one is the trickiest of the formulae that I have to develop
because the quarterly testing has to be completed in the two weeks prior to
the beginning of the next quarter. Which is why in the above it deducts C49
from the Sch-Comm_Date, where C49 = 14. Indirect(B49) returns 92 or 365/4.

Regards
Vicki

"Ms-Exl-Learner" wrote:

Just Confirm what is the value is present on B Column? Or provide some B
Column Values for examples.

--------------------
(Ms-Exl-Learner)
--------------------


"VickiMc" wrote:

My question;
I've seen somewhere that you can convert a text string into a formula using
the INDIRECT Function.

What I am constructing is a maintenance Schedule where some items need to be
serviced on a weekly basis, others monthly, quarterly, bi-annually, and
yearly. (Those are the only five rotations I require at the moment.) Once
constructed the format will take on the appearance of a calendar with
conditional formatting to highlight the dates the service falls on.

Each of the rotations require (I think) different formulas, so what I want
to do is have a list of the five different formulas minus the "=" sign in a
lookup table, then have it concatenate in the relevant cell as a formula.

EG in D7 if C7 contains "Weekly" then I want it to lookup the formula table
in A1:B5, find "Weekly" in ColA and return the corresponding formula in ColB
appended with an "=" sign to create a Formula.

FYI the formulas are working on dates, ie, depending on whether the result
falls between a period start date and a period end date determines whether or
not the result is displayed.

Regards
Vicki