Is there a way to lock the formulas on a series of linked excel spreadsheets so the formulas can't accidentally be deleted? I have it set up so that 4 identical spreadsheets with 12 tabs feed into one other spreadsheet and want to lock all the formulas. Is there an easy way to do this? Thanks! Dooma You can select which cells to lock and then enable worksheet protection. By default all cells on a sheet are locked when sheet protection is enabled. Hit CRTL + A(twice for 2003 users) to select all cells. Format>Cells>Protection. Uncheck "locked" and OK out. Now s...

I have a formula in a query which works fine until I tried to test it by deleting values in the form. Instead of nothing showing up, like how the formula should work, it showed ",X" The formula Other Fills: IIf([Other]=0,Null,[Other] & "X" & [Type]) & IIf([Other 2]=0,Null,"," & [Other 2] & "X" & [Type 2]) Example: [Other] = 2 [Type] = 4 [Other 2] = 3 [Type 2] = 5 Should look like 2X4,3X5 When [Other 2] and [Type 2] values are deleted it should look like 2X4 instead of 2X4,X The default value for the [Other] and [Other 2]...

...

We have a large PPV and I think it has to do with the fact that we went from using Standard Cost to Current Cost. Is it possible that items on older purchase orders that were created when we were using std cost are cosing these PPV's? If so, where do I update in the POP10310 to have the items use currect cost (FIFO Perpetual)? Thank you PPV accrued when you work on stadard costing and your purchase price is different. Purcahse order price is not having any impact on costing. However, if your item receipt price and invoice price are different and you did not choose the option to re...

how to write a formula? if cell =>24 multiple by 22. if cell is <24, multiply 24 by 22, multiply difference by 33 add together -- Kerri Where do I find the difference "Kerri Olsen" wrote: > how to write a formula? > if cell =>24 multiple by 22. if cell is <24, multiply 24 by 22, multiply > difference by 33 add together > -- > Kerri See Help for "IF worksheet function" I can't be more specific without a better description of the alternative calculations. Jerry "Kerri Olsen" wrote: > how to write a formula? > if cell ...

Is there a formula that will change another cells information w/o having a formula in that particular cell. For instance, the IF(N5=Removed" then J5=0) without putting a formula in j5? No. A cell formula cannot change another cell. You can write code to react to value changes in cell N5 and the *code* can change the value in J5 "Todd Nelson" wrote: > Is there a formula that will change another cells information w/o having a > formula in that particular cell. For instance, the IF(N5=Removed" then J5=0) > without putting a formula in j5? No, you cannot ...

I am a manager in a big office where agents log into their phones to get calls. We keep track of how many minutes an agent is "logged off" of their phone system. I would like to be able to have a formula to use in Excel to speed up the calculations for this info. Here is what the info looks like that I am given and what I need to calculate: Sunday, December 03, 2006 Agent Login Time Logout Time # of minutes logged out Doe, Jane 6:58am 8:51am (need to find # minutes from 9:39am-8:51am) Doe, Jane 9:39am ...

How do I enter a formula to look at a number and based on calculation round up or down to nearest hundreth? For example 421 would round down to 400 while 573 would round up to 600. try =round(a1,-2) where A1 has the number you want to round to the 100's "Jackie Morin" wrote: > How do I enter a formula to look at a number and based on calculation round > up or down to nearest hundreth? For example 421 would round down to 400 while > 573 would round up to 600. You could look up ROUND in Excel help. Try =ROUND(A2,-2) -- David Biddulph Jackie Morin wr...

We have a problem with the invoice matching activity in the system in regards to closed G/L periods. For example: - The receiving is done in period 2 (February) - Two weeks later the supplier invoice comes in with a different cost, it is now period 3 (March). Period 2 is now closed The system will flag these transactions and prevent the posting because the period is closed but will "place" them in the master posting with the date from the previous period. So it is smart enough to identify that the period is closed but dumb enough to generate the transactions wi...

I am trying to take a worksheet "apple" and copy it to a new workbook "banana". It works for another worksheet but not this one. I am highlighting the whole worksheet and copying it and then opening the workbook sheet and clicking the corner box and selecting paste. When it works correctly the cell would look like this: cell from "apple" ='sheet1'!A2 then when it is pasted over to "banana" ='C:\Documents and Settings\Desktop\PERSONAL\[apple.xls]Sheet1'!A2 now when i paste it over it just pastes what the cell displays which...

For excel sheet, is there any other way than the formula I put in cel B1 as: =If(AND(A1>100,A1<200),"Yes","No") i.e. I want to detect the value in A1 if it is >100 but <200 -- Message posted from http://www.ExcelForum.com i what's wrong with this kind of formula. Not really a beter way available. You may try =If((A1>100)*(A1<200),"Yes","No") >-----Original Message----- >For excel sheet, is there any other way than the formula I put in cell >B1 as: >=If(AND(A1>100,A1<200),"Yes","No") >i....

Hi, I have a few formulas embedded in my vb6 code which calculate and enter the results in the excel sheet. These formulas work for me and everyone at my workplace. But for some reason these formulas have recently stopped working for some of our model users in other countries. I noted that the problem is only with the formulas that are calculating the percentage, the rest of the formulas are working on the sheet. I also wanted to ask, is there a difference in using N/A and N?A in VB6? I should probably ask this question in the VB forum, but if anyone knows, please advise. Thanks, ...

I have a typical financial sheet where the columns sum (sum(a5:a15)) and the rows sum (sum(a5:h5)). The summation columns are locked and the sheet protected. Is there a technique whereby I can have the user insert a row which will keep the formats and row sum as well as changing the formula in the column sums? Thanks. Rick Hi Rick, The following method is not foolproof but generally it works OK. Insert a blank row between the bottom of data to be summed and the row with the sum formulas. Include the blank row in the sum formula. Protect the blank row along with the formulas and what...

We are currently running MSCRM 1.2 and GPS 8.0, with the MS Integration between them. Our Sales force uses various Inventory locations when placing orders within our GPS system. Because of business reasons we can't consolidate into a single Inventory Location as the Integration documentation suggests. The documentation allow says that it only supports the use of one Inventory location, which you set in the "Settings and Administration" tool. It appears that you can modify the BizTalk Mapping for the SalesOrderDetail channel to route a value from a custom field in MSCRM...

I have first names in one column and last names in a second column and I need to combine them in one column for an import. The formula I usually use is =A1&" "&B1 but for some reason it doesn't want to work for this spreadsheet. It's Office 2007 and I've saved the file as a regular worksheet. Any ideas? Never mind, it's just something with this particular spreadsheet. I copied and pasted the columns into a new one and it worked. "Brittany" wrote: > I have first names in one column and last names in a second column and I need >...

I am creating a spreadsheet for my acount payables and recievables. I want to designate a cell to keep a total. I need one cell whenever I add a figure it add to the total and another cell whenever I subtract a figure it will subtract from that same cell. I don't know if I understand exactly what you're asking. Might this be it? Enter this is B1: =SUM(A1:A100,C1,-D1) Where your total cell (B1), which is adding Column A, will increase by the number you enter in C1, and decrease by the number you enter in D1. -- HTH, RD -----------------------------------------------------------...

I have a row of birth dates on a sheet, and need a formula to determine how many are over 60 years old. The cell format for the dates is 11/30/2009 Try =IF(DATEDIF(A1,TODAY(),"y")>=60,"60 or above","") OR year to year comparison =IF(YEAR(TODAY())-YEAR(A1)>=60,"60 or above","") -- Jacob "Robert" wrote: > I have a row of birth dates on a sheet, and need a formula to determine how > many are over 60 years old. > > The cell format for the dates is 11/30/2009 > Assume your dates are ...

Right all you clever people, I need a formula for my tax spreadsheet who can help me? I'm doing my mileage. I can claim back 40p a mile for the first 4,00 miles, then 25p a mile after that. I've been using this formula for the 40p a mile: =D75*0.4 (D75 bein the "total miles" cell) Fairly straightforward, but how do I make it so it adds 0.4 for th first 4,000 then 0.25 for everything after that. Is this possible? Thanks in advance -- dancingbear7 ----------------------------------------------------------------------- dancingbear72's Profile: http://www.excelforum.co...

I am trying to sort a column of formulas that consist of cel references. Example: Column: Cell 1 holds: =D10 Cell 2 holds: =D11 Cell 3 holds: =D12 Data: D10=1 D11=2 D12=3 When I try to sort the column of formulas, the formulas change cel references to other cells and mess up the data. How can I sort a column of cells of which all of them contain formula and have the cells keep the references to the right place -- Message posted from http://www.ExcelForum.com Hi- If you write your formulae with absolute references rather tha relative references, you can sort them. Formulae should look ...

I have 5 sheets, Heath, Gary, John and Fred. They go to their sheet Heading: Date Question1 Question2 Question3 They ent 3/3 5 6 1 They enter a date for each monday its filled out. then enter a number between 0 and 10. I have a mastersheet. It has the same heading as above but with the sheet name in column b What i would like is in the master sheet is if in column4 row1 cell a date. How can i change the date in col 4 row 1 that when it looks at the other sheets it will display only the figures for that date? Hope you understan...

Hello, Im using the WorksheetFunction.Substitute code to change a series formula. But when a series sources data which is #NA, the code wont work, as the data is not visible on the chart. Anyway around this to change those series also? many thanks ahead. Ali - Convert the series type to xlArea first, then you'll be able to access the series. - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ Ali wrote: > Hello, > > Im using the WorksheetFunction.Substitute code to change a series formula. But when a series sources ...

For some strange reason my formulas do not work until I save the sheet.I am using 2007 with a file that was in 2003. Never had a problem before. It is in compatibility mode. when I enter the data nothing happens in the cell where the result is suppose to go. If I close and save the result shows up. If I open the file back up and take out the data I put in the result is still there, where it should be a zero. Something is screwed up. Multiple people have access to this file. Sorry about the post . I found the problem. Somebody changed calculations to manual "bob" <...

I have monthly worksheets that use drop down boxes to pick criteria from lists. The linked cell outputs a number based on what the chosen criteria was in the drop down box. Currently I use this outputted number on a summary sheet with a COUNTIF formula ie COUNTIF(Sept!$R$3:$R$200,2) to summarize monthly info. I would like to generate yearly info instead without having to generate monthly totals first. Is there a formula that can use both ranges - Jan:Dec and $R$3:$R$200 - with the criteria of the linked cell ie. 2? ...

Hello, I am stuck with this: If I want cells to display like 20-digit numbers in numerical form (that is, not the xxx+EXX way), formatting them as a text is okay. But then I need to add formulas and want the results to be displayed. But as the cells are text formatted, the result in the cell is of course the text of the formula plus the number. So my question is - in a cell I want e.g. "12345678901234567890-sum(A1,B1)" and I want the cell to display the resulting number, not the formula itself. How do I achieve this? The maximum precision for a number is 15 digits according to...

After we receive and order, we create the invoice with the create invoice button, however the order is still there as "new" or "pending". Is there any way to "close" the order as having been received? (CRM is expecting that you would be integrated to an accounting system) You could write a manual workflow rule to set the order status to Submitted and order status reason to (whatever); you may be able to get the workflow to trigger on something more useful like creating an invoice, but I haven't tried that Peter "Biyoojok" <biyoojok@gmail...