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
0
Utf
11/30/2009 2:59:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
1196 Views

Similar Articles

[PageSpeed] 13

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
0
Utf
11/30/2009 5:15:01 AM
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" <VickiMc@discussions.microsoft.com> wrote in message 
news:377A6853-4781-4A64-A87D-333E800715A9@microsoft.com...
> 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 


0
T
11/30/2009 5:21:30 AM
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
0
Utf
11/30/2009 6:51:01 AM
Reply:

Similar Artilces:

formula-percrntage
i am trying to figure out how to put a formula in that will be able to get a percentage of a number in one cell with the answer in another cell without showing the percent rate that was used to get the answer. Any help would be appreciated. Thanks Defining a name for the percentage is one method. Select any cell and Insert>Name>Define>Name qwerty Refers to 13% and OK In a cell enter =A1*qwerty Users can always snoop in to find the name if they choose. And remember.........if users can see the original number they can always reverse-engineer from the formula results. In s...

Array Formula not updating
I have a quite a large array formula: {=(INDEX('[Plant Movements Entry.xls]Data'!$E$2:$E$9997,MAX(('[Plant Movements Entry.xls]Data'!$A$2:$A$9997=A3)*ROW('[Plant Movements Entry.xls]Data'!$A$2:$A$9997))-CELL("row",'[Plant Movements Entry.xls]Data'!$A$2)+1))} for some reason it wont update properly unless the other workbook which it refers to is open, if the other book is not open it returns error values. its strange because i have used the same formula in other books with out incident can any one shed some light on why this may be? Cheers The pr...

entering new data in a saved spreadsheet without losing formulas?
How do I do this? Hi Jackie, As long as you do not overwrite the formulas, then any new data will not affect the formula. I suspect though that there is more to this question. Are you concerned about writing new data into a cell that has a formula in it? The way to stop a cell's data being overwritten is to put protection onto it. This means taking protection off all the other cells otherwise you will not be able to write data to any cell. First select the whole worksheet concerned, then go to Format\Cells and select the protection tab and unselect the 'locked' tick box,...

How to diffrentiate Text & number from a single cell????
Hi ther guyz............i a have small problem with the execl sheets........... my problem is e.g. i have alot of cells with a number and some text in it........and they both are in one/single cell (e.g. "878 queen st" in A1)....now what i want is to remove let's say "queen st" from each cell so that there is only number left in it.....................i cannot figure out any formula........if there is any..............need help imediately -- pack_card2000 ------------------------------------------------------------------------ pack_card2000's Profile: http://www...

Help with using data
Hi I would really appreciate some help with extracting data from Outlook. I have been building up a database of clients/prospectives, these are in various categories. I would like to extract/search on various fields as well as my own categories, all i can do with export is extract the whole lot to a tab delimited file. Or export categories to an rge file which I can't use. Any ideas out there? thanks Outlook's Search doesn't suffice? There are 3rd party search applications that might help. What sprcifically stops you from exporting to a format other than TSV? There are other...

Delete cell contents while retaining formulas
I want to delete cell contents from cells I select without deleting the formulas. How do I accomplish this? Thanks in advance.. Hi - select your range - hit F5, click 'Special' and check 'Constants' - now hit the DEL key -- Regards Frank Kabel Frankfurt, Germany "Dave A." <cyberayk@excite.com> schrieb im Newsbeitrag news:Oo4ybUJ2EHA.1144@TK2MSFTNGP09.phx.gbl... > I want to delete cell contents from cells I select without deleting the > formulas. How do I accomplish this? > > Thanks in advance.. > > ...

Run-time error 438
I'm trying to find out why this code is not working. With Worksheets("Multi-period Code data").ListBox1 .Clear For FillCount = 1 To Worksheets("ClassCodes").Range("E1") .AddItem Worksheets("ClassCodes").Range("C1").Offset(FillCount, 0) Next FillCount .ListIndex = Worksheets("Multi-period Code data").Range("O1").Value End With Just to clarify, there is a sheet named "Multi-period Code data" and ListBox1 exists on the sheet. The err...

Converting CRMDatetime to Text gives one hour difference
Hi All I have one custom entity with one CRM Date Time Field that accepts date and as well as Time. I use this to convert from date time to string CRMDateTime dt= new CRMDateTime(); dt=Convert.ToDateTime(new_date.Value).ToLocalTime(); But the problem I am facing is , it gives one hour difference like if the Date I saved is 3/29/2010 and time is 10:30 AM when i convert to string i get 3/29/2010 time 9:30 AM. Please help. Did you check your local settings on your client (Tools > Options > General > Timezone)? And the Timezone of the user you are using (System, an Administr...

SOS! SharePoint Install
I downloaded SharePoint Services 2.0 and installed it on my Windows Server 2003 Std. server where CRM 3.0 is installed. Now, I can't use CRM. When I double click the icon I get a message that says "The Server is Unavailable". Major disaster! Does anyone have any ideas? Thanks in advance. Did you reboot the server? "Lloyd" wrote: > I downloaded SharePoint Services 2.0 and installed it on my Windows Server > 2003 Std. server where CRM 3.0 is installed. Now, I can't use CRM. When I > double click the icon I get a message that says "The Server is...

Real Programmers (TM) use MSFT C# not Linux languages (sez an expert)
Don't take my word for it, see what this nationally syndicated author and computer programming guru says... and note the reader's comment at the very end...about Linux...I think he's talking about Linux. Anyway the takeaway executive summary of this article is simple: C# rulz. RL http://programmingzen.com/2010/06/23/how-microsoft-is-changing-the-programm= ing-world/ How Microsoft is changing the programming world Posted on Jun 23rd, 2010 in .NET, Programming Languages | 47 comments Several years ago I knew a programmer, we=92ll call him Joe, who fancied himsel...

If statement with a Vlookup
I am trying to build an IF formula, that first looks for the value in a column on another tab, if found leave blank, If not found, to display text (an "x"). I have tried variations of the following formula, unfortunately it says I have too many arguments for this function. =IF(ISNA(VLOOKUP(A2,'cardholder list'!A:E,3,FALSE))=TRUE,"x",VLOOKUP(A2,'cardholder list'!A:E,3,FALSE)= false, "") I have tried to interchange false inside the vlookup to true and not getting the desired result. I appreciate any suggestions you have. ...

Vlookup problems
I have Vlookups set up for a particular Spreadsheet. Each month I just drop the new data into the first column. Even though the number is formatted just like the previous month (i.e general, text, whatever) my vlookup formula shows #N/A. If i double click in the data cell and hit enter, the vlookup formula works. can you tell me why, or if there is a better way than clicking and hitting enter? Thanks! do you have your automatic calculation turned on? Under tools<options<calculation. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip...

Retaining Formats from VLOOKUP
I have a Vlookup formula in worksheet1 that looks up values from worksheet2. These values in worksheet2 can either be percentages or currency amounts. Is it possible to have vlookup (or via code) to have return value from the lookup to retain the format from worksheet2? Thanks Mike Formulas can return values--not formatting. Any chance you can use Format|conditional formatting to duplicate the formatting? (Use the same rules in both spots.) Squid wrote: > > I have a Vlookup formula in worksheet1 that looks up > values from worksheet2. These values in worksheet2 can > e...

Can you store clipboard text and pick it up by copy it again?
I have a catch 22 - impossible actions here: A/ I want to have the sheet protected so users can't ruin all cells in the sheet B/ I have to Unprotect the sheet in the sub event Worksheet_Activate to be able to do some format macros C/ I want users to allow to copy text from one sheet and paste in this one. D/ The clipboard wipes out by the Unprotect command E/ The user have nothing to paste in... Is there a solution? /Regards --------------------------------------------------------------- ------------------------- Earl Kiosterud wrote: --------------- Imbecill, CutCopyMode gets turned...

Visio Text (Number) Automation
Hi. I'm creating an Organization Chart for my company. I have the org charts already setup with names, shapes, etc; however, I would like to add the total number or employees in each group, which would add up to larger group, and finally all the way to the top which will have the total number for all employees. i would like to somehow automate this process like excel does wtih a formula (=techdept+accoutningdept) which would add the numbers i manually filled in the tech and accouting dept, but would add those number together for the group above that. Thanks. ...

help with lookup formula (SHEET 1 attatched)
I need to make a formula using a lookup. The first spreadsheet is a inventory (ALO INVENTORY JP). The second (P&G 103) is a chart tha tells number of gallons of alcohol per foot/inch/fraction. On ALO INVENTORY JP I insert the # of feet, inches, and the fraction i cells R5 and T5. I want these numbers to lookup the number of gallon off of the P&G 103 worksheet. For example: P&G 103 (on inventory sheet) is 26ft 4 1/2 inches, i should look-up to be 34572 gal. for the 26'4" and 55 gal. for the 1/2" These two numbers should be added together and multiplied by the numbe ...

Legend and text entry
I am on Excel 2003 and need to create a simple pie chart. I am asked to put into the legend the data, individual dollar amounts and then the total in the Legend. Is that possible and if so how can I do that without effecting my chart. Thank you. Hi, If you are asking how to put the values of the datapoints into the Legend - Excel does not do this by default. But you can do it by creating a custom name for each of the data points. Suppose you have the names of the data points in A1:A5 and the values you are plotting in B1:B5. Insert a column between A:B and enter the formula =A1...

ROUNDED NUMBERS IN FORMULA'S
Can anybody help me to set up Excel so that it uses rounded numbers in formulas? I have got the cell formatted to 'show' the number rounded to two decimal places but the complete root number (shown in the formula bar), is used! Eg. 1.5642 (1.56 shown) x 4 = 6.2568 (6.25 shown) I want to multiply 1.56 x 4 = 6.24, without going into each individual cell and removing the unrequired numbers. I hope that somebody can help me - even if it is to tell me to stop trying because it's maybe not possible. I thank anybody in advance for any constructive response. bing1080 =round(A1...

Merging text files with excel
Hello All. How can I merge several text files with the same format into one Excel sheet in one go ? I am importing them singly but it would be much better to merge them together before opening. Many thanks for any help ! Maybe you can use some old DOS commands. Shell to a command prompt and issue an old DOS command to concatenate the Text files. Windows start button|Run (or flying windows r as a shortcut!) win98: Command WinXP/NT: CMD Go to that folder: C: (to change to that drive) cd\myfolder1\myfolder2\myfolder3 to change to that folder copy *.txt All.Files Ren All.Files All....

Excel formula problem SUMPRODUCT
Hi Guys, I have a problem with a formula adding up rows against 2 parameters. I am trying to add up rows on a report that 1) show that the actio against it is "open" and its target completion date is overdue. I have tried the following formula but it will not work. =SUMPRODUCT(--(H5:H47="OPEN")*(G5:G47="<TODAY())) This formula works if I substitute the date part with another tex parameter but will have nothing to do with dates even if I put in specific date that I know matches one from the column, I still get 0 Any ideas. Thanks in advance And -- Message ...

selected text highlighting issue
Windows XP SP3. Office 2003 fully updated. This has always bugged me so I figured I'd finally ask the question. I run 2 monitors and work on multiple things at once. If I have a Word doc open (or notepad or some other editor) and select a block of text and then move to another application, the text in the Word doc no longer appears selected. This is extremely annoying at times. Just because my Word doc is no longer the active application doesn't mean that I don't want to see what I've selected. And if it's so that I know it's not the active application, well t...

Can I copy formulas "as they are" when they are not static
Hi, I have numerous cells with formulas in them, and I would like to transfer them to other location without formulas changing. They need to be dynamic before and after, but not during this transfer Don't ask me why ;-) -Sirritys Find and replace all "=" with "~", move and change back -- Kind regards, Niek Otten Microsoft MVP - Excel "Sirritys" <aki.koikkalainen@hotmail.com> wrote in message news:1154937837.006411.154950@m73g2000cwd.googlegroups.com... | Hi, | | I have numerous cells with formulas in them, and I would like to | transfer them to...

convert money plus files into money 2007
I downloaded the new trailversion of money Plus. Now I do not like the new version of money Plus . I had money 2007, but now I cannot convert my money files from the Money plus version into the Money 2007 version. What can I do? When you converted your M2007 file to M+ there was a screen message telling you that it had made a backup. You *of course* made a note of this!?!? If you didn't go and look for a file *.m16. Rename this as *.mny and reinstall M2007. Of course you will have to reinput all transactions since the conversion. -- Regards Bob Peel, Microsoft MVP - Money F...

Wrap text, Merge cells
I have merged 2 cells in a column, wrapped the text. There are actually 20 rows of data ( sentences) in one cell After the 18th row within that merged cell the data is no longer visible. Is there a limit to how high a cell can be ( mine is at 350, but can be made a whole lot large, not that the data is visible though) or how many "rows" wihin a merged and wrapped cell are possible? Stumped in IN. thanks for any info on this subject A row's height (with or without merged cells) cannot exceed 409. Also, a cell can display only about 1000 characters even though it can hold ov...

Problem importing data from text file
Hi, I have a comma delimited file that has twenty column names followed by six rows of data. I am trying to import this into Excel. When I do this, the data imports entirely into row one. It does import into difference cells but there is one row with 120 cells of data. I was expecting it two "Wrap" and start importing the data into cell A2. Can anyone help with this? How do I show the break between column headers and data? Thanks, Chuck ...