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?
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...IIF formula
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"
Other Fills: IIf([Other]=0,Null,[Other] & "X" & [Type]) & IIf([Other
2]=0,Null,"," & [Other 2] & "X" & [Type 2])
[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]...Formula ErrorHi
...Purchase Price Variance
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)?
PPV accrued when you work on stadard costing and your purchase price is
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...need help with formula #2
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
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
See Help for "IF worksheet function"
I can't be more specific without a better description of the alternative
"Kerri Olsen" wrote:
> how to write a formula?
> if cell ...Need help w/ formula
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 ...Need Help creating a formula****PLEASE
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
Doe, Jane 6:58am 8:51am (need to
find # minutes from 9:39am-8:51am)
Doe, Jane 9:39am ...Formula Help 02-02-10
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.
Jackie Morin wr...Purchase Receipt posting to closed period
We have a problem with the invoice matching activity in the system in regards
to closed G/L periods.
- 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...Excel not pasting the formula or reference
I am trying to take a worksheet "apple" and copy it to a new workbook
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"
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...Formula for > but <
For excel sheet, is there any other way than the formula I put in cel
i.e. I want to detect the value in A1 if it is >100 but <200
Message posted from http://www.ExcelForum.com
what's wrong with this kind of formula. Not really a beter
way available. You may try
>For excel sheet, is there any other way than the formula
I put in cell
>i....VB6 formula not working in excel
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,
...Inserting rows into sheet that contains a formula
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
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
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...Inventory Site/LocationCode Order Lines on MSCRM1.2 to GPS8.0
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
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...Formula Help
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.
> I have first names in one column and last names in a second column and I need
>...cell formulas #2
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:
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.
-----------------------------------------------------------...Year formula 11-30-09
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
=IF(DATEDIF(A1,TODAY(),"y")>=60,"60 or above","")
OR year to year comparison
=IF(YEAR(TODAY())-YEAR(A1)>=60,"60 or above","")
> 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 ...Formula needed #2
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
dancingbear72's Profile: http://www.excelforum.co...How to succesfully sort column of formulas?
I am trying to sort a column of formulas that consist of cel
Cell 1 holds: =D10
Cell 2 holds: =D11
Cell 3 holds: =D12
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
If you write your formulae with absolute references rather tha
relative references, you can sort them. Formulae should look ...Help to create a formulae
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...Changing series formula
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.
Convert the series type to xlArea first, then you'll be able to access
Jon Peltier, Microsoft Excel MVP
> Im using the WorksheetFunction.Substitute code to change a series formula. But when a series sources ...formula only works when file is saved.
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
"bob" <...Need help with Multi-workbook COUNTIF formula!!
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?
...Formatting cells for big numbers and formulas ...without the formu
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...Closing orders
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