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
...what does this mean
for some unknown reason my OE keeps giving me this message when i go to post
Outlook Express could not post your message. Protocol: NNTP, Server
Response: '441 Posting Failed ("From" Header not in Internet Syntax.)',
Port: 119, Secure(SSL): No, Server Error: 441, Error Number: 0x800CCCA9
"MiND SpiKe" <ScReaM@mE.c0M> wrote in message
> for some unknown reason my OE keeps giving me this message when i go to
> post :
> Outlook Express could not post your message. Protocol: NNTP, Server...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...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...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...Meaning of Symbol (Icon) in Sent Folder
This may be a very simple-minded question, but I can’t seem to find reference
to it anywhere.
1. There is a symbol in the Sent folder, in the “Sort by: Icon” column that
looks like an opened envelope with a blue “I” in a circle. What does this
icon mean? It seems to appear for some, but not all, mail I’ve sent to
people within my organization (on the Outlook system and in the mailbox. In
fact, sometimes it will appear for the same person at some times, but not at
others. It never appears for people outside of our network. I also noticed
that when it appears, the message includes a...copy exact formulas
Is there a way to copy formulas EXACTLY as they appear, without the cell
references moving around?
I mean, if I have the following formula in cell A1:
I want to copy it to the cell C1, but I want it to be the exact formula
=5+A5+A6, not to change the reference to the C column
I know a way to do it is to go in the cell, select the formulas "text" and
then paste it, but that doesnt work in case of multiple cells..
"Guillermo" <email@example.com> schrieb im Newsbeitrag
> Is there a way to copy...Formulas List
How do I print a list of all formulas contained in a worksheet?
Tools/Options, and on one of the tabs there's a box to display formulas. Click
that, do your printing, then change the setting back. The shortcut key for
this is CTRL+` (the single back-quote to the left of the 1 key on the upper
row of the keyboard).
On Sun, 12 Dec 2004 20:05:04 -0800, "Earl" <Earl@discussions.microsoft.com>
>How do I print a list of all formulas contained in a worksheet?
"Earl" <Earl@discussions.microsoft.com> wrote...
>How do I print a list of all formulas ...