Stopping Excel from editing my Formulas
I am trying to get excel to stop automatically editing my formulas when I
delete cells. Like right now I have a two lists of data and I'm going through
to find the same data points between the lists. So I have on list in column B
and one in column C. So my formula in column D is =if(B1=C1, "TRUE") but
every time I go to delete cells from column C and move them to the top my
formula changes to =if(B1=!REF, "True") and if I deleted say one cell the
next line row in column D the formula will be =if(B2=C1, "TRUE") How Can I
stop Excel from doing this?
What is the formula that will add the content of a cell on one worksheet
(sheet 1) to the contents of another worksheet (sheet 2)?
Assuming your have data in A1 of sheet1 and data in A1 of sheet2
In B1 of sheet2 enter =A1 & sheet1!A1
If you wish a space between enter as =A1 & " " & sheet1!A1
If by "add" you mean "sum"
In B1 enter =SUM(A1,sheet1!A1)
Gord Dibben MS Excel MVP
On Thu, 19 Oct 2006 15:43:06 -0500, "C J" <email@example.com> wrote:
>What is the formula that will add the content of a cell on one ...An issue regarding the formula
It's OK to copy the formula from one cell to another one within the
same sheet. How about that to copy the formula from one Excel file to
another one? It seems only the value of that cell will be copied to
that file but not the formula inside it. I even have tried to copy the
corr. formula within that again but just the copied formula will not
work successfully in there.
Thks & Rgds,
HuaMin's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29423
View this thr...Amend formula?
I have the following formula which works well:
SUMPRODUCT((Main!$G$4:$G$2885 = "I")*(Main!T4:T2885))
but i want to amend it slightly to check if column B equals GL as well -
think that is: (Main!$B$4:$B$2885 = "GL")
but i don't know how!!
SUMPRODUCT works by multiply numbers (row-by-row) and then adding those
individual products up. Your numbers can be actual values and/or logical
expressions. The reason your formula works is because the logical expression
(Main!$G$4:$G$2885 = "I")
evaluates to either TRUE or FALSE whi...CONCATENATE formula
When using the CONCATENATE formula, how do I get a space between the 2 fields
in the result cell?
or slightly less typing
=A1 & " " & B1
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"jkeeton" <firstname.lastname@example.org> wrote in message
> When using the CONCATENATE formula, how do I get a space between the 2
> in the result cell?
...formula that meets two criteria and sums?
I need a formula that will determine if information in a database meets two
criteria and then total the number of rows that are correct. The column
titles are Event type and New/Repeat. I need to be able to distinguish if an
event is sports, smerf, or group and then new or repeat. Any help would be
Array-entered (CTRL-SHIFT-ENTER, or CMD-RETURN):
In article <3FD727FE-C380-4924-A366-388967B15BA7@microsoft.com>,
"...How do I set a cell value based on a formula in another cell?
How do I set a cell value based on a formula in another cell?
=IF(D4="P.B.A.",G8,0) ,This works to set the value in the cell the formula
is in, say cell A1.
I would like to have this formula reside in cell A1 but set the value in
cell A2 to (G8,0).
Also it would be helpful if I could lock cell A2 based on a False condition
from this formula in cell A1.
I note that this entry is also asked and answered in Programming.
> How do I set a cell value based on a formula in another cell?
> =IF(D4="P.B.A.",G8,0) ,This w...RPC over HTTP requires additional authentication
When I configure Outlook 2003 for RPC over HTTP it requires me to enter
username/password again. I have already entered it when I logged into the
domain so why should I have to re-enter it? It is very irritating. I am
assuming there is some workaround for this annoyance. Does anybody know how
to get around this problem or have you all accepted retyping your
Any suggestions would be greatly appreciated.
On Sat, 12 Mar 2005 03:35:02 -0800, "Slacker"
>When I configure Outlook 2003 for RPC ...I need help creating a formula
Hi, I really hope someone out there can help me, I am trying to create a
that will allow me to calculate freight charges. I want to be able to input a
weight and a zone and have it come back with the cost. This is basically
what the sheet looks like now,
Sheet 1 Cell C27 (Weight) 5
Sheet 1 Cell C29 (Zone) 51
Sheet 1 Cell C31-Formula
Now based on my data on sheet two using 5lbs and zone 51 should
make the answer $13.31. The weights from 1 to 50 are in Column A2:A51
on sheet 2 and the Zones are B1:D:1 and the charges are in Columns
B2:B51, C2:C51,D2:D51,E2:E51. None of the formulas I h...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 ...Finetune formula to get currency format?
I've managed to put together two different formulas from two different
sources and it seems to be working okay except in terms of the format
of the numbers.
The formula is this:
=IF(G2="?",TEXT(G2,"$0")&" ttl unknown",G2*I2)
The "$0" in there is a remnant from the original. I don't know what
to do with it so I've left it in.
The purpose of this formula is to deal with situations where a list
price is unknown and a user puts a question mark in G2. This formula
goes into the totals column but there is no total known so th...Exchange Version Required for Front-end Server
Can an Exchange 2003 Standard edition become a Front-end server or does it
have to be Enterprise Editon?
Unlike with Exchange 2000, Exchange 2003 Standard Edition can be a front-end
MVP - Exchange
"Protecting the world from PSTs and brick backups!"
"Zack" <email@example.com> wrote in message
> Can an Exchange 2003 Standard edition become a Front-end server or does it
> have to be Enterprise Editon?
"Zack" <firstname.lastname@example.org> wrote:
>Ca...=Sheet2!$A$1 from string to formula
Cell A1 in Sheet1 holds the result of a concatenated
string and reads: =Sheet2!$A$1.
How can I make Excel display the value of A1 in sheet 2 in
stead of the formula =Sheet2!$A$1 (as a string)?
Can anyone help?
try the function INDIRECT, e.g.
Niels Govers wrote:
> Cell A1 in Sheet1 holds the result of a concatenated
> string and reads: =Sheet2!$A$1.
> How can I make Excel display the value of A1 in sheet 2 in
> stead of the formula =Sheet2!$A$1 (as a string)?
> Can anyone help?
You can build your ...Modify a formula
This is showing how much of a novice I am, but I'm learning. I need to
change this formula so that it not only shows the message if multiples
of 84 is not entered but if multiples of 61 is not entered into the
same cell. Thanks.
=IF(MOD(F21,84)<>0,"Qty in this Row is not in multiples of 84","")
=IF(MOD(F21,84)=0,"",IF(MOD(F21,61)=0,"","Qty in this Row is not in
multiples of 84 or 61"))
"Eintsein_mc2" <email@example.com> wrote in message
news:firstname.lastname@example.org...Excel why cant A(row()) replace A1 or A2 to repet formula dn rows
I want to copy formula down many rows but don't want to have to retype row
numbers at every different row
I assume you're wanting to "build" a cell reference that increments as you
copy a formula.
Post the formula you want to copy and include a more detailed explanation.
Microsoft Excel MVP
"jimmy" <email@example.com> wrote in message
>I want to copy formula down many rows but don't want to have to retype row
> numbers at every different row
...making a field required when selecting case resolved
On the case entity, I'd like to make a few fields required only when
the user select Actions>resolved. If they select to resolve, these
fields would become required, but they can save the case without this
data while working the case. How would I control the field
...Excel Formula #3
I am new at excell 2003. I am working on a spread sheet and I cannot find the
formula I need.
My question is based on the following information:
Homework average (94) = 15%, Project average (95) = 40%, and Test average =
(45%). What is the Final grade?
I want to thank you advance for your help.
One way ..
Assuming the marks and weighted %
are in B2:C4, as below:
(a mark of 80 is assumed for "Test average")
Put in B5: =SUMPRODUCT(B2:B4,C2:C4)
For the sample data, B5 will return: 88.1
GMT+8, 1� 22' N 103� 45' E
xde...Help with setting a formula
I am attempting to setup two columns. One column is adding charges. The
other is showing the charges deducted from a starting balance. I am trying
to keep track of a cash card as I am spending the funds.
Thanks in advance for your assistance
Put your initial balance in A1 and nothing in B1
In A2 enter:
=A1-B2 and copy down
As you enter charges in B2 and going down, they will be debitted in column A
and the balance will appear.
Gary''s Student - gsnu200791
> I am attempting to setup two columns. One column is adding charges. The
...Formula for seperating data
I have a long list of names that i need to seperate out into the teams on
On the "team" worksheet in column I, I have the team names and I want each
on a seperate worksheet.
So on worksheet Green I want to get all member, columns A-C and G-H and have
them on the Green worksheet.
I have lots of data and do not want to use copy and past.
"eechris" <firstname.lastname@example.org> wrote in message
>I have a long list of names that i need to seperate out in...see the cells the formula relates to by double clicking the cell
this is probably a simple preference, but I cannot find it on my new Excel
program. When I double click a cell with a formula, I am used to seeing it
highlight the cells that are related to this formula. That does not happen
with my new Exel. Can someone tell which preference to change? Thanks
<Tools> <Options> <Edit> tab,
And check or uncheck "Edit Directly In cell".
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------...Show all formulas?
Is there a way th have all formulas in a sheet shown, so I can print it out
with formulas showing?
Ctrl + `
Andy Wiggins FCCA
Excel, Access and VBA Consultancy
"Confused Man" <email@example.com> wrote in message
> Hello all,
> Is there a way th have all formulas in a sheet shown, so I can print it
> with formulas showing?
> Confused Man
"Confused Man" <firstname.lastname@example.org>ha scritto nel messaggio
FcCdnSC...Formulas showing in cell???
I keep getting a formula showing in the cell after I edit i
(occasionally). Show formulas is turned off and edit in cell is on.
How can I make the formula go awaw and simply show the results whic
when edited the results shows correctly?
Message posted from http://www.ExcelForum.com
probably the cell is formated as 'Text' change the cell format to
'General' and re-enter your formula
> I keep getting a formula showing in the cell after I edit it
> (occasionally). Show formulas is turned off and edit in cell is on.
>...open/import Lotus wk4 files into Excel 2007 w\ formulae intact
I do not access to Lotus Smartsuite and need to import an old Lotus wk4
spreadsheet into Excel with the formuale intact. It would be huge hassle to
recreate it and I am not going to invest in Smartsuite.
2007 does not support lotus. so you need to find someone with lotus or check
in to open office. it's free.
> I do not access to Lotus Smartsuite and need to import an old Lotus wk4
> spreadsheet into Excel with the formuale intact. It would be huge hassle to
> recreate it and I am not going to invest i...Recover Deleted Items.... Require permission
We are using Outlook 2007 and Exchange 2003 SP3 (I believe) and one of the
employee accidently deleted a folder. When I click on "Deleted Items" and
then select Tools\Recover Deleted Items, I don't see the folder listed but
when I click on Junk E-mail and then select Tools\Recover Deleted Items and I
found the folder listed in there. So I select the folder to recover I got a
message that I require to have permission to recover some or all of the items
or contact Admin something like that. How can I find or set up a permission
to enable to recover the folder?
Than...Require value for "regarding" in Outlook email promotion?
We want the Outlook client to require users to select a value (contact,
account, etc.) for the "regarding" field when an email is promoted. If I
customize the CRM “email” entity so that its “regardingobjectid” attribute is
“Business Required”, will that do the trick?