Frequently used formulas

Is there a way to access frequently used formulas easily? I have a report 
where I need to import the data and then type in the same formula each time 
in order to create analyzable results. Is there a quicker way?

Thanks
0
Utf
1/14/2010 7:23:02 PM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
1229 Views

Similar Articles

[PageSpeed] 42

I'm not sure this will work well for you, but it might be worth a try.

Why not create a workbook with just the formulas in place, but without the 
data?  Then you'd open that workbook, import the data into it and save it out 
with a new name?

If that's not good for you, perhaps simply copying the formulas into a 
notepad (.txt) file and saving it so that you can open it and copy and paste 
from it later?

One other way I could think of IF the formula is always typed in the same 
way, into the same cell(s) and using references to the same cells to get 
its/their values from would be to Record a Macro while entering the 
formula/formulas -- when you start recording it, give it a 
easy-to-remember-meaningful name and save it into your PERSONAL.xls workbook, 
so that it is always available to you no matter what workbook you have open.  
Then execute the macro/macros when you need to enter those exact same 
formulas in the same exact locations in a new workbook.

"Kate" wrote:

> Is there a way to access frequently used formulas easily? I have a report 
> where I need to import the data and then type in the same formula each time 
> in order to create analyzable results. Is there a quicker way?
> 
> Thanks
0
Utf
1/14/2010 8:13:01 PM
Quicker could be a set of macros that wrote the formulas for you.

Really quick would be to create a Template to import the data into.

The formulas would already be in place.......just need the data.

To create the Template, open a new workbook.

Format and enter the formulas where you will need them.

Save As Template(*.xlt) in 2003 and earlier  or (*.xltx) in 2007.

Sort of quick would be to have a sheet with the formulas written preceded by
an apostrophe to make them Text.

When needed, copy and remove the apsotrophe.


Gord Dibben  MS Excel MVP


On Thu, 14 Jan 2010 11:23:02 -0800, Kate <Kate@discussions.microsoft.com>
wrote:

>Is there a way to access frequently used formulas easily? I have a report 
>where I need to import the data and then type in the same formula each time 
>in order to create analyzable results. Is there a quicker way?
>
>Thanks

0
Gord
1/14/2010 8:44:20 PM
Reply:

Similar Artilces:

How do I create a dashboard using Excel 2003?
I understand from a Microsoft article that I can create dashboards using Excel 2003. Can someone show me how? Visit Charley Kyd's site: http://exceluser.com Note that a dashboard is a way to present useful information, graphic and tabular, within a single screen or page. It is not a way to display fancy dials and gauges which sacrifice information for glitter. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jennifer D" <Jennifer D@discussions.microsoft.com> wrote in mes...

Find what control is using a data item
I built a form, then deleted 2 columns from the source table and now I get a popup asking for the value of those 2 columns. The problem is, I don't use those columns so I need to find what on the form is refferencing the deleted columns. I have looked at the control drop down on the properties window and the tab order window and can't find a control with either one of the missing column names. How do I determine what is trying to refference the deleted columns? I found the problem, the column was still referenced in the underlying query that fed the form. "MeSteve"...

CHECK BOX CONTROL USING EXCEL 2000
I am working on a worksheet that has over 2300 rows. I would like to have one field on each row to contain a true or false value. I'd like to have a check box being referenced to each cell. I'd like to be able to copy the control and have it maintain a relative cell value in the link cell property. Right now when I copy the control to the other 2,300 rows, the link cell is a4 and every control uses the same value at a4. Cell a4 is the only cell that changes values when any check box is clicked. How do I conect the check box control to a specific/different cell without ma...

Read file info using SMO locks database!
Hi! I'm using SMO from C# to restore a database from a backup file. Since I want to re-use the same datafiles I try to read the datafiles in my destination database and call RelocateFile to use these filenames during the restore. Everyting works fine except when I try to read the current datafilenames (see code below). If I skip this code and just hardcode the paths in relocatefile everything works fine. Database db = svr.Databases[destinationDBName]; foreach (FileGroup group in db.FileGroups) foreach (DataFile file in group.Files) Console.WriteLine...

IF formula problem
I need to add categories to a downloaded bank statement. I would use something like Pascal's CASE formula: CASE(lookup value)= a: do x b: do y c: do z etc... Excel doesn't have such formula. Therefore, I use the following nested IF formula: =IF(ISERROR(SEARCH('Netbank Cat names'!$A$2,B73)),IF(ISERROR(SEARCH('Netbank Cat names'!$A$3,B73)),IF(ISERROR(SEARCH('Netbank Cat names'!$A$4,B73)),IF(ISERROR(SEARCH('Netbank Cat names'!$A$5,B73)),IF(ISERROR(SEARCH('Netbank Cat names'!$A$6,B73)),IF(ISERROR(SEARCH('Netbank Cat names'!$A$7,B73)),"&...

How to render modified XML DOM to HTML document when using CHtmlVi
Hi, I used CHtmlView component for my app. I navigated to an XML file and then retrieved the XML DOM using IDispatchEx (ref: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnwebteam/html/webteam05072001.asp Straight XML). The XML DOM was appended with a new element but the change doesn't reflect in the view. How do I update the HTML Document without re-loading the file (as changes are made in the DOM and not the file). ...

Using result from or funtcion in if statement
i have a formula =OR(G2="DTA",G2="LTA") in cell AI2 I have an other formula =IF(G2="Multiplan",LOOKUP(U2,'Commission Rates'!$A$2:$A$46,'Commission Rates'!$C$2:$C$46),IF(G2="Health Care Inc",IF(X2>=11,95.84,IF(X2<5,0,LOOKUP(Z2,'Commission Rates'$A$2:$A$46'Commission Rates'!$C$2:$C$46),))),IF(AI2="TRUE",IF(H2>=11,111.81,-8),"error colm"))) when AI2 is true this should return either 111.81 or -8 but is not where have i gone wrong thanks colm Colm, Try IF(AI2=TRUE... without quotes. HTH ...

Sendinf personalised mails to a group using Outlook
Hi All, I would like to know how to send personalised mails to a group (keeping the groups in bcc) using outlook 2003/2007. Please share your thoughts. regards, Jaseel You have stated contradictory points. BCC does not "personalise" it just hides the recipient list. If you want to personalise, ie use data fields like first_name and last_name, you need to do a mailmerge to email. There are many posts here on how to do that. Regards Judy Gleeson MVP Outlook in Canberra, Australia "Jassy" <Jassy@discussions.microsoft.com> wrote in mes...

Use Dll created from .Net
Can I use a dll created from .Net in a Visual C++ 6.0 project? ...

How do you average text formulas??
I have this formula which calucates time differences =TEXT(F70-B70,"d:h:mm") format= 1:12:45 Everytime I use the "average(h7.h20)" the answer equals "#Div/0" I'm trying to average all these formulas, how do I average these cells that contain the =TEXT(F70-B70,"d:h:mm") format Hi! Why use the TEXT function? Why not just: =F70-B70 And custom format as d:h:mm. Biff >-----Original Message----- >I have this formula which calucates time differences =TEXT (F70-B70,"d:h:mm") >format= 1:12:45 > >Everytime I use the "ave...

I want a formula to ignore text values in cell references
If text is input into some cells referenced by a formula I want the formula to ignore whatever text it finds and just calculate the result of the numbers in cells. How do i do this? Presently the formula displays #VALUE! where there is text in one or more of the cells referenced. Use ISNUMBER(): In place of A1 use =IF(ISNUMBER(A1),A1,"") -- Gary''s Student "Russellrupert" wrote: > If text is input into some cells referenced by a formula I want the formula > to ignore whatever text it finds and just calculate the result of the numbers > in cells...

using indirect to return a named range?
I'm having a mental block on how to do this. I have a boatload of named ranges in my workbook. I have one worksheet that has two data validation cells and a graph. I want the two data validation cells to be used to select the named range to use to populate the graph series. My named ranges pull from a large variety of locations (not orderly) so I put the named range names in a table format for easier reference; here is an example with named ranges by month and location- the user selects a month and location, and the graph should use the named range with the name in the ...

How can I use a form (Excel) to add a column of data to the right.
How can I use a form (Excel) to add a column of data to the right of a list? It's a simple matter to add a column to the left of a list by writing a macro that inserts a column and then pastes the values. However I want to have the data pasted on the right of a list, if only so that I can easily run a chart from it. ...

Link to other slides from table using c#
Hi, I'm looking to create links from a certain slide to another, and I need those links to be from within a table. I need it to be something like this: var pptApp = new PowerPoint.Application(); var presentation = pptApp.Presentations.Add(Office.MsoTriState.msoFalse); var slides = presentation.Slides; slides.InsertFromFile(@"C:\Users\Tng1\Documents\PresentationTemplate.potx" , 0, 1); var titleSlide = slides[1]; titleSlide.Name="TitleSlide" ; var tableSlide = slides[2]; tableSlide.Name = "TableSlide" ; va...

Formula Problem
Hi I'm struggling with a formula in an excel sheet I'm working on. I recently created a time sheet for the people in my work. One person however get public holidays a different method from everyone else - she only gets half a working day. My initial formula that works fine is =IF(OR(B12="A/L",B12="S/L",B12="P/H"),7/24,IF(B12="FLEXI",0,C12-B12- D12)) My ammended ones is =IF(OR(B12="A/L",B12="S/L"),7/24,IF(B12="P/H",3.5/24),IF(B12="FLEXI", 0,C12-B12-D12)) However it doesn't want to w...

Filter listbox using combo box
I have a listbox on a form. The listbox row source is a query. It pulls in fields such as Work Request ID, Category, Status, Assigned To etc. On the same form I have an option group with 6 options (Category, Status, Building, Floor etc). When one of these are selected - it populates a combo box with the data. For example - if the user selects OptionCategory then the combo box lists all the Categories to choose from. Once the user has selected one of the six options and then selects an item in the combo box - I want to filter the listbox to show only those records. For example, the us...

Can't save Excel using Save button,
When I try to save an Excel spreadsheet, I get a message saying "GWXL97.xla could not be found. Check spelling of file name and verify that the file location is correct." I have never named any of files by that name and don't know what it refers to. I have to hit the "X" button on the upper right hand corner and wait for it to ask me if I want to save it, then go through the save process. This happens even if I try to save changes to an existing excel document This may help... xl: close button is dimmed after groupwise is installed http://support.microsoft.com...

Using RTF In E-mail
In Outlook 2000, how can one create or reply to an e-mail in rich text format, so that one could use font colors, etc.? You can use the Format menu when you have Outlook set as the e-mail editor or use the e-mail Toolbar when you have Word set as the editor. Note that RTF is the least compatible mail format and that upgrading a message format on a reply isn't considered nice. If you have specific needs to still upgrade the message format (for instance to use tables) use HTML-format instead. -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -FREE tool; ...

How do I delete recently used files on the file menu in Publisher.
You can set the number of recently used files in Word. Is this possible in Publisher 2000? Can I set it so it doesn't save them to the file menu? Kater Here wrote: > You can set the number of recently used files in Word. Is > this possible in Publisher 2000? Can I set it so it > doesn't save them to the file menu? ================================= After you read the following article you may decide the MRUs are not so bad after all. (313454) HOW TO: Clear the Most Recently Used List ( MRU) in Office Programs http://support.microsoft.com/?kbid=313454 -- John Inzer retur...

using checkmark symbol shows only # not check mark Excel 2003
I am using the Dingbat font. All stars and other symbols are OK, however, when I use the checkmark, or X symbol it only inserts the a number and not the symbol. Thanks. check the obvious - is the column wide enough ? otherwise what have you actually got in the cell ? Steve On Fri, 08 Sep 2006 09:36:02 +0100, Michael Abraham = <MichaelAbraham@discussions.microsoft.com> wrote: > I am using the Dingbat font. All stars and other symbols are OK, howev= er, > when I use the checkmark, or X symbol it only inserts the a number and= = > not > the symbol. Thanks. ...

Need Macro to autofill formula to last row with data
I have a worksheet which may contain data from A4 to A16. The first row A4 always contains data. I have formulas in B4 to L4. I need a macro to autofill the formulas to the last row filled in col A4:A16. I have a chart that uses these formulas so if I fill all the rows past the last filled row in col A then the chart X axis expands. Therefore, if the formulas dont go past the last filled row from A4 to A16 then the chart populates correctly. Can someome tell me the macro for this? Much appreciated. Joe M. Sub Auto_Fill() Dim lRow As Long With ActiveSheet lRow =...

What's the best way to diagram a supply chain using Visio?
What's the best way to diagram a supply chain using Visio? ...

Need help to explain this formula
Anyone can explain this formula please? =LOOKUP(99^99,--("0"&MID(C11,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C11&"0123456789")),ROW($1:$10000)))) Thanks in advance E The formula will return the first set of consecutive digits in a string. If C11 contains: abc123r56t78 the formula will return: 123 ...

Using Automation to Send HTML...
The following code used to work fine in Outlook 2000, now with Outlook 2002 it doesn't. Now it leaves a BLANK body. Can anyone tell me why this no longer works? Also, we try the same thing with an RTF type file as the body (.body property), and it just puts junk in the body and does not interpret it as a RTF file. Thanks. Bob ***************************************************** oOutlook = CREATEOBJECT("outlook.application") oMail = oOutLook.CreateItem(0) WITH m.oMail m.oRecipient = .Recipients.Add("test@test.com") m.oRecipient.type = 1 .Subject = &quo...

Moving mailboxes using Active Directory Users and Computers
This is a multi-part message in MIME format. ------=_NextPart_000_0022_01C61DC8.6FAD4950 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Question: When moving mailboxes using AD Users and Computers from = Exchange server A to Exchange Server B, what will be the effect on users = that currently have their mailbox open in Microsoft Outlook? Some of the = users have Outlook 2003 and some of them have Outlook 2002. Is it best = to do the move when noone is on? but the thing is in this organization, = we have so laptop users that may not be on...