Dragging a Cell Changes Formula

I am creating a spreadsheet which will be protected with a table of
cells unlocked for the user to fill in. There are formulas which
reference the unlocked cells. Is there anyway to prevent these formulas
from changing if the user drags (or cut-and-pastes) the unlocked cell
(ie, if they type data in the wrong cell and then drag that data to the
correct cell, any formula reference these two cells gets modified)?

Thanks!


---
Message posted from http://www.ExcelForum.com/

0
1/8/2004 8:51:43 PM
excel.misc 78881 articles. 5 followers. Follow

8 Replies
326 Views

Similar Articles

[PageSpeed] 41

Janet,

Make the formulas "absolute"

=A1 will adjust when it's moved or dragged anywhere.
=$A1 will "lock" the column
=A$1 will "lock" the row
=$A$1 will lock both the row and column.

John

"JanetW >" <<JanetW.zqdo5@excelforum-nospam.com> wrote in message
news:JanetW.zqdo5@excelforum-nospam.com...
> I am creating a spreadsheet which will be protected with a table of
> cells unlocked for the user to fill in. There are formulas which
> reference the unlocked cells. Is there anyway to prevent these formulas
> from changing if the user drags (or cut-and-pastes) the unlocked cell
> (ie, if they type data in the wrong cell and then drag that data to the
> correct cell, any formula reference these two cells gets modified)?
>
> Thanks!
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
jwilson (359)
1/8/2004 8:58:41 PM
"JanetW >" <<JanetW.zqdo5@excelforum-nospam.com> wrote:
> I am creating a spreadsheet which will be protected with a table of
> cells unlocked for the user to fill in. There are formulas which
> reference the unlocked cells. Is there anyway to prevent these formulas
> from changing if the user drags (or cut-and-pastes) the unlocked cell
> (ie, if they type data in the wrong cell and then drag that data to the
> correct cell, any formula reference these two cells gets modified)?
>
> Thanks!

Tools/Options/Edit/Edit directly in cell

This isn't exactly what you asked for, but it will prevent dragging and
dropping of cells from being used at all.

Tim C


0
timclainc (28)
1/8/2004 9:12:57 PM
John,

A misunderstanding...the cell with the formula isn't being moved. That
cell is locked. The cell being referenced to (by a formula in another
cell) could potentiall be moved because it's unlocked. But thanks
anyway!

Tim,

I'm assuming you meant:

Tools/Options/Edit/Allow cell drag and drop

But the problem with that is that it appears to be an application
specific option, not a file specific one (I'm always curious why MS put
both types of options in one dialog box without any indication of which
is which!). Therefore, everyone accessing the file would need to set
this option...and then it would apply to ALL their Excel files, not
just this one.


---
Message posted from http://www.ExcelForum.com/

0
1/8/2004 9:58:17 PM
If you want to go through the trouble, you could use INDIRECT.

If your formula is:
=A1*B1
You could, for example, enter "A1" in F1 and "B1" in G1 (no quotes), and
then change the formula to:
=INDIRECT(F1)*INDIRECT(G1)

(I know there's a proper syntax so that you can enter Indirect only once, I
just can't remember it now.)

HTH,

RD

"JanetW >" <<JanetW.zqdo5@excelforum-nospam.com> wrote in message
news:JanetW.zqdo5@excelforum-nospam.com...
> I am creating a spreadsheet which will be protected with a table of
> cells unlocked for the user to fill in. There are formulas which
> reference the unlocked cells. Is there anyway to prevent these formulas
> from changing if the user drags (or cut-and-pastes) the unlocked cell
> (ie, if they type data in the wrong cell and then drag that data to the
> correct cell, any formula reference these two cells gets modified)?
>
> Thanks!
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
ragdyer1 (4060)
1/8/2004 10:18:43 PM
Yes, it looks like INDIRECT is what I was looking for. The help even
says you could put the text directly in the INDIRECT function. So to
simplify your example:

INDIRECT("A1")*INDIRECT("B1")

Then you can skip the intermediate cells. Unfortunately, even if I had
know this trick before I started, it would have been extremely
impractical to use it on my current project (too complex as it is!).

But thanks for pointing me in the right direction. I will definitely be
using this on future (smaller) projects!


---
Message posted from http://www.ExcelForum.com/

0
1/8/2004 11:51:48 PM
Hi Janet,

Did you find a solution for this problem after all ?
I'm having the same problem.
Help would be much appreciated.

tnx

Wim


---
Message posted from http://www.ExcelForum.com/

0
1/15/2004 2:27:26 PM
The INDIRECT fuction is definitely what I was looking for, but for my
current project, especially for how far along it is, is far too complex
to go back and rewrite all the formulas to use this as a way to
reference cells within formulas. But it appears to be the only way to
provide an absolute reference to a cell that will persist through
either cut-n-paste or drag-n-drop of the referenced cell.

One thing I plan on experimenting with on the next project is how
INDIRECT can be efficiently used in situations when, for example, you
are filling formulas down a column. I think you should be able to use
INDIRECT along with ROW, COLUMN, and INDEX functions to create a
formula with absolute cell location references, but that will also
adjust properly as you fill the formula to adjacent cells.

As a simple example, if I wanted to add a number in column A to a
number in column B, and then fill down the result in column C, you
could write in C1:

=INDIRECT("A"&ROW())+INDIRECT("B"&ROW())

When you fill down this formula, it will still add the numbers from the
current row, but the formula would also stay intact if you drag-n-drop
a value in A or B to a new row.

Hope that helps!


wim wrote:
> *Hi Janet,
> 
> Did you find a solution for this problem after all ?
> I'm having the same problem.
> Help would be much appreciated.
> 
> tnx
> 
> Wim *


---
Message posted from http://www.ExcelForum.com/

0
1/15/2004 5:09:52 PM
See if this example is of use to you
  Build TOC Another Approach  (use of INDIRECT)
  http://www.mvps.org/dmcritchie/excel/buildtoc2.htm
-
HTH,
David McRitchie, Microsoft MVP - Excel


0
dmcritchie (2586)
1/23/2004 12:38:09 PM
Reply:

Similar Artilces:

Prevent clicking on a cell
I want to run the code below to prevent a range of cells from being selected if the Range("Q7") = 1. I have all cells on the worksheet locked but the user must be able to click on the locked cells to trigger a userform so I have to check Select Locked Cells. So is there any way make the Range("B5:C5") unselectable? If Range("Q7") = 1 Then Range("B5:C5").Locked = True End If Hi, >So is there any way make the > Range("B5:C5") unselectable? No but you can stop them staying there. Private Sub Worksheet_...

send the same e-mail with one or two fields changed.......
I would like to send the same e-mail to many differnet people with one or two fields changed (for example the name of recipient and the date).How canthis be done?? I would also like to be able to save the e-mail and use it again and again. can anyone help cheers john If you have Word installed and it's the same version as Outlook (both 2003, for example), you can do a mail merge between the two. This would allow you to set up the text the way you want it to, and you can save the document for future use. Look at the following page for further information: http://www.slipstick.com/con...

if cell is text move left one column
ColB is a long list with sections names followed by category codes I need to move the text into colA leaving colB with codes only (all numbers) ColB. Doors 940590 555998 447006 447008 810697 810705 810706 810707 Windows 619435 525691 525692 Try Sub Macro1() Dim lngRow As Long For lngRow = 1 To Cells(Rows.Count, "B").End(xlUp).Row If Not IsNumeric(Range("B" & lngRow)) Then Range("A" & lngRow).Value = Range("B" & lngRow).Text Range("B" & lngRow).Value = "" End If Next End Sub -- Jacob ...

How do I set up a daily average of unit sales formula
More info required. -- HTH RP (remove nothere from the email address if mailing direct) "jim m" <jim m@discussions.microsoft.com> wrote in message news:7E6D4510-97C1-42D4-A402-5590201C6065@microsoft.com... > ...

Special Pasting a work book with many sheets and formulas
I have a workbook with many sheets that all have formulas and links to other data. I want to save the workbook as another name with all the worksheets keeping the values only (no links or formulas). Is there a quick way to do this for everysheet without having to special paste every sheet in the workbook. So can I save everysheets data values at workbook level. See this page for a code example http://www.rondebruin.nl/values.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "lex63" <lex63@discussions.microsoft.com> wrote in message news:ED708...

why does sorting change a scatter plot graph?
Why does the way a spread sheet is sorted change the look of a scatter plot graph??? the graph is just a plot of two points, (X, Y) and these two points are definded by two collumns for a given row. The two collumns don't change, and the row all stays together, so why does it change where points are plotted out on the graph when you re-sort it? AndrewT420 - Usually, for an XY (Scatter) chart, with values of X in a column and corresponding values of Y in an adjacent column, for three or more points, Excel assumes (correctly) "Series in Columns." But, when you have only...

Too Many IF Statements Nesting Error (Excel Formula Loop w/o VBA)
Hello Excel Problem Gurus, First of all, let me thank you in advance. I find it exemplary that you all can devote time to helping others who are having issues with their work. Hopefully one day I can be at a mentor level, and help others too. Hope you can help! I have an issue where I don't know how to write the formula that I need without going over on the nesting. The current formula that I have is as follows: =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""),"No Data",IF(V7="Yes",&qu...

Changing Prices in HQ.
Hi, I have this little issue. I want to change the put items on promotion using the price wizard using HQ. Unfortunately if I have stores who has differents prices for a same item the wizard do not make the proper change becuase it use the price already stored in the master table. Does anyone saw this issue before? Who was solved?. Thks in advance for your help. Rgds Rodrigo Hi there, The easiest way to look after this is to not change any data on the ITEM in HQ, but to simply do the worksheet for altering the sale price and then send it to the respective stores. Then in the works...

How do I extend a underline across an entire cell?
When working on a financial statement, I was curious how to 1. Have a line extend across an entire cell even if the number is only 2-3 digits and 2. How to apply a double line under a number without using the = sign in the following cell? Hi Lindsay Look on the formatting toolbar for Borders -- Regards Ron de Bruin http://www.rondebruin.nl "Lindsay" <Lindsay@discussions.microsoft.com> wrote in message news:F4C9ED6C-7F2D-4277-86CC-6FA46D315DA5@microsoft.com... > When working on a financial statement, I was curious how to 1. Have a line > extend across an entire ce...

Content of emails is changing without any reason !
Hallo I changed operating system last week. From Win XP to Win 7. Used to work with Outlook Express at full satisfaction. I could transfer most of my emails automatically with export/import features of Microsoft software. But I suddenly discover 1 very big problem (bug ???) I am used to work with several maps, and hereby go to several levels deep. Such as : Saved mails Companyname Projectname Date of action Department Activity Name of patient Different emails So sometimes maps can go several levels deep. When I check ema...

Saving toolbar changes
After spending a lot of time to customize a toolbar in Excel 2003, it disappears when opening up another file, or starting the app again. I repeatedly change it, save it as XLB, XLT, save multiple copies in every possible location...but the damn thing always defaults to its own toolbar settings. This makes toolbars almost useless. How can one insist that PPT use YOUR toolbar setting, rather than its own default Thanks. Hi Jeff, If I have a lot of tool bar changes to make, I close all the workbook that are not hidden then unhide my personal.xls from the Window menu. I don't know why...

Separating Date and Time in a cell
I have a column of cells in the format "11/01/02 06:21". I would like to separate the text into 2 cells - one with the date and the other with the time. My attempts with LEFT and RIGHT have been unsuccesful. Thanks for your help Sameer --- Message posted from http://www.ExcelForum.com/ For the date use =INT(A1) replace A1 with the first cell of your range for time =MOD(A1,1) you probably have to reformat the first to mm/dd/yy (or whatever the setting is) and hh:mm Note that you can do this by just using format but if you want to compare to other cells with just pure d...

Change cannot be saved due to sharing violation
Hello I've this message while saving the excel file even if no change ha been done to the file. There is no share on this file (exclusive use) File resides on a network drive It's very disturbing Thanks for your help Vobiscu -- Vobiscu ----------------------------------------------------------------------- Vobiscum's Profile: http://www.msusenet.com/member.php?userid=245 View this thread: http://www.msusenet.com/t-187102186 http://support.microsoft.com/default.aspx?scid=kb;en-us;328170 Thanks for your answer, I will try tomorrow noo Vobiscu -- Vobiscu ----------------...

Changing query execution sequence
Hi all, I got a spreadsheet which would execute a bunch of queries. It's noted that the queries are executing in the sequence of when it was added to the spreadsheet. Does anyone out there know of a way to switch the order without deleting and recreating them? Thanks! Wing ...

cell contents revert to 0 when i click on the next cell
I put a number into a cell click on the next cell and the first cell reverts to 0. If I format to number with 2 decimal places it will be ok but when I try to take out decimal places it goes back to zero, Help please You haven't said what number you are trying to put into the cell, but I suspect that the number is less than 0.5. A quick test shows that if you set the cell to no decimal places then enter a number less than 0.5 it is displayed 'rounded down' so it will show as zero, if it's 0.5 or above it displays as 1. If you need to put numbers less than 0.5 into youe c...

Accommodating for empty cells in this formula?
I have a formula in cell H21, for example, reads like this: =IF($G21<>"",($H20-$G21),"") is there a way to adjust the formula so that an empty cell in G21 doesn't give the #VALUE! in subsequent cells in column H? Just to give a similar example, this formula =SUMIF(A1:A9,"<>0") adjusts for any and all empty cells in A2 to A9. It no longer matters if any of the cells are empty, the formula correctly gives the correct addition of A1 plust a sum of everything between A2 to A10 without any #VALUE! results. Was hoping to have the formula above als...

Help With Margin Formula
Hello, I need help with a margin forumla (calculated from retail). Say I have a cost of $10.00, and I need the formula to calculate a 40% margin from retail. So the retail should end up at $16.67. Not sure how to get from $10.00 to $16.66, I just know the cost and the margin I need to make. Thanks JR =A1/(100%-40%) -- Kind regards, Niek Otten "JR" <gaspower@aol.com> wrote in message news:eGszf.424$2O6.53@newssvr12.news.prodigy.com... > Hello, > I need help with a margin forumla (calculated from retail). Say I have a > cost of $10.00, and I need the formul...

Automatic changes in cells
Hi for some reason I now have to save my work for any formlas etc to change when I update a worsheet, how can I stop this as it is a pain and sometimes I need to do changes to see how they work before saving the work. Many thanks Click on Tools | Options | Calculation tab and set to Automatic calculation, as it is probably set to Manual. You can press F9 to force a recalculation under a manual setting. Make sure you save the file with the Automatic setting, to avoid it happening next time. Hope this helps. Pete On Feb 1, 11:42=A0am, Office 2004 Test Drive User <heepenm...@yahoo.co.u...

Change the text of a shape rather than its master
Hi, I build custom masters by mixing two general shapes, say square and circle together, and have text on both the shapes. But after I drop an instance of the master into a page, I cannot modify the text of the instance. To do so, I need to modify the text on the master, which is non-sense for me. How to change the text of a shape without modifying its master? Thanks! How are you doing this? By code or by the UI? Are you grouping the shapes? If you drag two shapes to the stencil, it will group the shapes. So instead of a square and a circle you have three shapes. A Square, Circle and the...

cell colour change when set markers are reached
i need to get a cell to change colour when markers are reached eg a qualification lasts 12 months. what i want to do is have the cell change from yellow to orange to red as the expiry date gets closer. If column A contains expiry dates then select column A, Formats>Conditional Formatting>formula1: =DATEDIF(TODAY(),A1,"m")<1 red for 1 month Click Add button, formula2: =DATEDIF(TODAY(),A1,"m")<2 orange for 2 month Click Add button, formula3: =DATEDIF(TODAY(),A1,"m")<3 yellow for 3 month Adjust number of months as you like! Regards,...

Changing ip address of exchange server #2
Hi, I have a back-end server and a smtp server in DMZ. I want to change ip address of back-end server. are there any issues? all incoming and outgoing emails are going via smart host. Hi, No issues at all as long as you remember to change all the references to this server in your firewall, SMTP scanner etc. Leif "Jack Dorson" <JackDorson@discussions.microsoft.com> wrote in message news:FE5927A1-D20D-4C6B-991F-2E1EFD19434D@microsoft.com... > Hi, > > I have a back-end server and a smtp server in DMZ. > > I want to change ip address of back-end server. are ...

Calculating on alphabetic cell content
Hi, A selection of 4 different letters in a column representing different values to be used in a formula shall be run through. The calculated result of each cell in the column shall be placed in the cell next to the read one that holds the letter. Thanks in advance. Hi i think you're after the COUNTIF function with your column of letters in A1:A100 and the letter you're interested in in C1 then in D1 =COUNTIF(A1:A100,C1) this will count the number of times the value in C1 occurs in your range. If this isn't what you're after, could you type out a few examples of your ...

Removing text from cells leaving numbers (help with function)
I need a function that will remove all text from a cell and just leav numbers. Formatting cells to number does not work. For example if I have: (Sired] Tennessee 37013 (herein I just want 37013 left. Anybody know a function to resolve this -- Message posted from http://www.ExcelForum.com The following will strip the text from the active cell and place the number in the adjcent cell one column to the left. If there are subsequent numbers in the original string you will get erroneous results. Put the cursor on the cell to be processed and run the macro. ********************************...

Sorting Cells by Colors
Hi all, Is it possible to write a VBA code to sort excel cells by colors, and the followed by other criterias, as in the normal sort? Thank you in advance. Hi SwiftCode, See Chip Pearson's Sorting By Color page at: http://www.cpearson.com/excel/SortByColor.htm --- Regards, Norman "swiftcode" <swiftcode@discussions.microsoft.com> wrote in message news:FC1550A7-A8DD-4EC0-B171-F1DB4373C35C@microsoft.com... > Hi all, > > Is it possible to write a VBA code to sort excel cells by colors, and the > followed by other criterias, as in the normal sort?...

searching a cell for a contained text word
Is it possible to search a cell for a key word or words contained in text made of multiple words enabling the user to than create a pivot table using the collected key word or words as data? Doug K ...