Lock formula to cell

I want a formula to lock to a cell so that when I add or delete a column the
formula will always remain with say column H

I know if the sheet is Password protected this will prevent this happening
but that is no use when I have to unprotect the sheet to delete a column.

Anyone got thoughts on this?

regards
Pat


0
2/6/2004 12:57:04 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
735 Views

Similar Articles

[PageSpeed] 9

Hi Pat
without using VBA you can't achieve this

Frank

Pat wrote:
> I want a formula to lock to a cell so that when I add or delete a
> column the formula will always remain with say column H
> 
> I know if the sheet is Password protected this will prevent this
> happening but that is no use when I have to unprotect the sheet to
> delete a column. 
> 
> Anyone got thoughts on this?
> 
> regards
> Pat


0
frank.kabel (11126)
2/6/2004 1:04:26 PM
Hello Frank,

Would you happen to know the VBA code?

Pat


"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:e5BICHL7DHA.1948@TK2MSFTNGP12.phx.gbl...
> Hi Pat
> without using VBA you can't achieve this
>
> Frank
>
> Pat wrote:
> > I want a formula to lock to a cell so that when I add or delete a
> > column the formula will always remain with say column H
> >
> > I know if the sheet is Password protected this will prevent this
> > happening but that is no use when I have to unprotect the sheet to
> > delete a column.
> >
> > Anyone got thoughts on this?
> >
> > regards
> > Pat
>
>


0
2/6/2004 2:08:22 PM
Hi Pat
a little bit complicated and not fully tested but try the following
code. Put it in your worksheet module (you have to change the
sheetname)

Private Sub Worksheet_Change(ByVal Target As Range)
Const fixed_name = "lock_formula"
Const refer_str = "=Sheet1!$H$1"
Dim rng As Range
Dim save_formula

If IsError(Evaluate(fixed_name)) Then
    ActiveWorkbook.Names.Add Name:=CStr(fixed_name),
RefersTo:=CStr(refer_str)
Else
    If CStr(ActiveWorkbook.Names(CStr(fixed_name)).RefersTo) <>
CStr(refer_str) Then
        On Error GoTo CleanUp
        Application.EnableEvents = False
        Set rng = Evaluate(fixed_name)
        save_formula = rng.Formula
        rng.Value = ""
        Set rng = Range(CStr(refer_str))
        rng.Formula = save_formula
        ActiveWorkbook.Names(CStr(fixed_name)).RefersTo =
CStr(refer_str)
    Else
        'do nothing
    End If
End If

CleanUp:
    Application.EnableEvents = True
End Sub

HTH
Frank

Pat wrote:
> Hello Frank,
>
> Would you happen to know the VBA code?
>
> Pat
>
>
> "Frank Kabel" <frank.kabel@freenet.de> wrote in message
> news:e5BICHL7DHA.1948@TK2MSFTNGP12.phx.gbl...
>> Hi Pat
>> without using VBA you can't achieve this
>>
>> Frank
>>
>> Pat wrote:
>>> I want a formula to lock to a cell so that when I add or delete a
>>> column the formula will always remain with say column H
>>>
>>> I know if the sheet is Password protected this will prevent this
>>> happening but that is no use when I have to unprotect the sheet to
>>> delete a column.
>>>
>>> Anyone got thoughts on this?
>>>
>>> regards
>>> Pat


0
frank.kabel (11126)
2/6/2004 2:52:04 PM
Frank

That seems to work nicely.  I have changed the range to:

Const refer_str = "=Sheet1!$H$1:$H$800"

If I want to extend the range to cover another group of cells would it
just a matter of altering the code to:

Const refer_str = "=Sheet1!$H$1:$H$800&Sheet1$R$1:$R$800 "

Pat



"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:#NROLDM7DHA.2656@TK2MSFTNGP11.phx.gbl...
> Hi Pat
> a little bit complicated and not fully tested but try the following
> code. Put it in your worksheet module (you have to change the
> sheetname)
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Const fixed_name = "lock_formula"
> Const refer_str = "=Sheet1!$H$1"
> Dim rng As Range
> Dim save_formula
>
> If IsError(Evaluate(fixed_name)) Then
>     ActiveWorkbook.Names.Add Name:=CStr(fixed_name),
> RefersTo:=CStr(refer_str)
> Else
>     If CStr(ActiveWorkbook.Names(CStr(fixed_name)).RefersTo) <>
> CStr(refer_str) Then
>         On Error GoTo CleanUp
>         Application.EnableEvents = False
>         Set rng = Evaluate(fixed_name)
>         save_formula = rng.Formula
>         rng.Value = ""
>         Set rng = Range(CStr(refer_str))
>         rng.Formula = save_formula
>         ActiveWorkbook.Names(CStr(fixed_name)).RefersTo =
> CStr(refer_str)
>     Else
>         'do nothing
>     End If
> End If
>
> CleanUp:
>     Application.EnableEvents = True
> End Sub
>
> HTH
> Frank
>
> Pat wrote:
> > Hello Frank,
> >
> > Would you happen to know the VBA code?
> >
> > Pat
> >
> >
> > "Frank Kabel" <frank.kabel@freenet.de> wrote in message
> > news:e5BICHL7DHA.1948@TK2MSFTNGP12.phx.gbl...
> >> Hi Pat
> >> without using VBA you can't achieve this
> >>
> >> Frank
> >>
> >> Pat wrote:
> >>> I want a formula to lock to a cell so that when I add or delete a
> >>> column the formula will always remain with say column H
> >>>
> >>> I know if the sheet is Password protected this will prevent this
> >>> happening but that is no use when I have to unprotect the sheet to
> >>> delete a column.
> >>>
> >>> Anyone got thoughts on this?
> >>>
> >>> regards
> >>> Pat
>
>


0
2/6/2004 4:56:42 PM
Reply:

Similar Artilces:

blinking cell #3
Is there a possibility to make a cell content blink under certain conditions (as in conditional formatinf for example...)? Thanks No -- Ian -- "ktisqj" <ktisqj@discussions.microsoft.com> wrote in message news:6F14A597-AD6B-49DC-BB71-B3A259F11908@microsoft.com... > Is there a possibility to make a cell content blink under certain > conditions > (as in conditional formatinf for example...)? > Thanks It can be done by using code, but its a really bad idea. Using such code causes more problems than enough. Blinking or flashing are things that were not regarde...

Get Microsoft RMS Sales on Cell Phones
I also now have a component that allows users to get a list of stores along with sales for the day for each store and total sales on cell phones. The sales are live based on the Headquarters update schedule. This allows a user to get sales any time of day from any location as long as they have cell phone coverage. If you are interested in purchasing this component, e-mail me at mark.colbert@echosurf.com. Thank you, Mark A. Colbert ...

Extend formulae
In S2 I have =IF(R2="","",R2*0.034+0.2) extended down to S9. In T2 I have =IF(R2="","",R2-S2) extended down to T9. In U2 I have =T2. In U3 I have =IF(R2="","",U2+T3) extended down to U9. In other words a running total. When I enter an amount (�) in R10, cells S10 and T10 automatically update but not U10. Why won't the formula in U9 extend to U10? Sandy Hi Sandy Change the formula in U3 and down to: =IF(R3="","",U2+T3) Regards, Per "Sandy" <sandy_stephen@DELETEhotmail.com> skre...

How to tell if a cell is protected
I have nine workbooks I have combined into one workbook using the drag-and-drop system as described by Bernard Liengme on 11/25/07. All the workbooks were single worksheet's that my company uses as forms for personnel files. I don't want to reinvent the wheel, but I am trying to make some of the work easier. What I am doing is setting up all the worksheets to autofill the redundant information on each sheet, i.e. name, address, phone number, etc. I am using the formula "=sheet!cell". It is working on some sheets and on others is does not. On some of the sheets, o...

Non-Printing Cells
Can anyone tell me whether it is possible to format individual cells so that they do not print. I do not want to hide rows or columns as I want to be able to see the contents on the screen. Thanks Andrew, Almost what you want... Use a custom number format of 3 semicolons ;;; The content of the cell will appear in the formula bar but not on the screen. Regards, Jim Cone San Francisco, CA "Andrew Mackenzie" <a_d_mackenzie@hotmail.com> wrote in message news:OXsY9zBZEHA.556@tk2msftngp13.phx.gbl... > Can anyone tell me whether it is possible to format individual cells so...

Date formulas needed
Three cells involved: E4: date entered manually by user F4: date next IPP due G4: date next Review due Need formulas for F4 and G4 to satisfy these conditions: 1) E4 is empty: F4, G4 = "" 2) E4 <= 21 days from today: F4 = E4 + 21 days, G4 = E4 + 90 days 3) Else: F4 = E4 + 6 months, G4 = E4 + 1 year -- David F4: =IF(E4="","",IF(E4<=TODAY()+21,TODAY()+21,DATE(YEAR(TODAY()),MONTH(TODAY())+ 6,DAY(TODAY())))) G4:=IF(E4="","",IF(E4<=TODAY()+21,TODAY()+90,DATE(YEAR(TODAY())+1,MONTH(TODA Y()),DAY(TODAY())))) be aware though adding 6 mont...

Maintain Formatting with Formulas
I am writing a formula that references multiple cells as the possible source cell depending on certain parameters being met. My question is whether anyone knows how to maintain the formatting in the display cell? My possible source cells all contain different number formats (i.e. -1234.0 and ##00.0E-+00). Is there a way to write this into the formula, or is there a formatting option that allows you to keep the source cell's format? Any help would be great! Use the TEXT function to apply the same format to the values from the referenced cells as was used on those referen...

locking up
This might be more of a GDI question, but since I'm in MFC, I'll ask: Would BitBlt-ing to the screen repeatedly tend to lock up a program, after alt+tabbing to another application? If so, why? This seems to be happening to me, and I want to get to the bottom of the problem. What I have is either a fade loop or a screen-size redraw, both based on a timer and bitblt. Quite often, after I leave the program to go to another app, I cannot return and must "end task". "Steve Russell" <srussell@removethisinnernet.net> wrote in message news:%23YFL5Ks%23F...

Desktop Alert Locks Computer
Greetings, I am seeing a little quirk on some of my desktops. It only happens occasionally, but it happens to different models of PC. Not all the time, but once in awhile, I have users that complain of their PC freezing. The only remedy is to do a hard reboot. I have noticed in these instances, that the desktop mail alert ( the preview that pops up by the system clock when you get a new mail) is showing when it froze. If I go into Outlook and turn off the desktop alert, then the PC freezing stops. So, I know that the Desktop Alert is the cause of the problem. Has anyone else ever seen this? Th...

paste a formula to a range of cells
hi all, Excel newbie here--If I have a range of cells in a row, all with the same formula (using relative cell addresses), and want to extend this row another ten cells to the right say, using the same formula, how do I do it. I've tried copying one cell, and pasting it these 10 cells (after I've highlighted them) but that doesn't work. thanks in advance Doug: That should work...Highlight the cell with the formula in it (NOT a blank cell), hit CTRL-C, then highlight those 10 cells on the Right, and hit CTRL-V...This should do it...Any questions, drop me an email... Steve &...

Formula rounding incorrectly but not consistently
I am trying to calculate a discount on an exchange rate (rate x .75%) but when I do a manual calculation to verify the figures, some are rounding up or down without rhymn or reason. Example: Rate Manual Calc Excel Calc Should Be 1.8775 1.891581 1.8915 1.8916 (didn't round up) 1.2995 1.309246 1.3093 1.3092 (rounded up but shouldn't have) I have formatted the cells and verified the correct number of decimals. My exchange formula is: rate-(rate*discount) Should I have to enter a rounding formula? I did create one and it worked by itself but ...

Contents automatically entered into another cell
working on an estimate and trying to figure out a way to take the results of one cell and have its contents be automatically entered into another cell. I have a subtotal line item for a particular category, I am wanting to create a cover page showing the various catergories but not all of the breakdown. (It is simple to keep track of right now, but as the estimate gets more involved, changes become harder to remember). I'm trying to create a reference to the cover page that will reflect the sub-total line item from the detailed breakdown. So, if $3.23 is shown in the deatailed br...

Lock formula to cell
I want a formula to lock to a cell so that when I add or delete a column the formula will always remain with say column H I know if the sheet is Password protected this will prevent this happening but that is no use when I have to unprotect the sheet to delete a column. Anyone got thoughts on this? regards Pat Hi Pat without using VBA you can't achieve this Frank Pat wrote: > I want a formula to lock to a cell so that when I add or delete a > column the formula will always remain with say column H > > I know if the sheet is Password protected this will prevent this >...

can I use 2 formulas in 1 cell and display both results
Hi there, I was wondering if its possible to have 2 formulas in one cell. Such as vlookup one value and then vlookup another value and display both results in one cell. Thanks You cane use: =VLOOKUP(...)&"-"&VLOOKUP(...) HTH -- AP "chris" <chris@discussions.microsoft.com> a �crit dans le message de news:30B112DA-D57C-4498-BBB0-CF75F883F6BC@microsoft.com... > Hi there, > > I was wondering if its possible to have 2 formulas in one cell. > > Such as vlookup one value and then vlookup another value and display both > results in one cell. &...

Copy and Paste a formula as Values in a fixed Range as a loop statement using VBA
I have 3 work sheets i.e. a "Summary" sheet, a "Phase1" and a "Phase2" sheet The Phase1 and Phase2 sheets are a result of certain parameters that are entered separately for each of the Phases that produces the results in them. All the 3 sheets have fixed ranges and they are A2:AC151, the dates are within the range C3:AC150 for each Phase. All the 3 sheets have the same structure something like this A B C D E SAP NO CostCenter Dec-03 Jan-04 Feb-04 422100 DTF-Office 60,000 186,000 174,000 422140 DTF-Office 40,000 124,000 116,000...

locked excel File ??
Hi . I am working with my colleges with one Excel file in a shared file system. Sometime - when I open the file, there is the message "locked from College" - but he is definetly out of office and shut down his PC. We are using Excel 2003 + Excel 2007. What can I do?? THnks for help Susan ...

formula sheet contains an existing name
I have a worksheet that when I try to copy a tab I keep getting 3 error messages that says "A formula sheet you want to move/copy contains the name "", which already exists on the destination worksheet. Do you want to use this version of the name?" If I click yes, I get 3 more of the same message with different doc names. If clicking no I get a box that says name conflict, name exists on destination sheet. Then it lists the old name: & asks for a new name. If I enter a new name, nothing happens. HELP!!! ...

Formula R1C1 in VBA
Hi, I have the following code in VBA to give me some variance % results. Although the rows are always static the columns are not: ActiveCell.FormulaR1C1 = "=SUM(R[-34]C-R[-17]C)/R[-17]C" ActiveCell.Offset(1, 0).Select ActiveCell.FormulaR1C1 = "=SUM(R[-32]C-R[-15]C)/R[-15]C" ActiveCell.Offset(1, 0).Select ActiveCell.FormulaR1C1 = "=SUM(R[-30]C-R[-13]C)/R[-13]C" What I want this to do is to start in row 45, in the column i am in (worked out prior to this in the macro) and put in a formula which basically in Excel would read: ...

Keeping text within the cell
Hi all, Mr. Newbie here, How does one format the cell so that when text is entered it does not flow across the worksheet or resize the cell? I would prefer to keep the cell the same size and not have the text run into the next column and then just mouse over the cell to read what is in that cell. Is this possible? Thanks Tim Foster Make an entry in the cell to the right (even a space). In article <rie3d059vtotqt4pihh14u0n6r34mqau1t@4ax.com>, Tim Foster <opyis@shaw.ca> wrote: > Hi all, Mr. Newbie here, > > How does one format the cell so that when text is entered i...

Locked out of database
Not sure what happened but get a message saying "database has been converted from a prior version of Access by using DAO compactdatabase and is now in a partially converted state." It says I should be able to import tables into a new data base but when I try this it just gives the same message about converting and the data base just doesn't open. Please help because lots at stake, have to do a report based on these tables so am waiting with anticipation!!!x Hi Pete, Usually this means you are opening an earlier version of Access in a new version. Make a copy of the data...

how may I copy color of a other cell
Hello, I need a function that copy also the full format/color/size of a cell Example A1 = USD 12'500.00 (monetary size, 12 fat, blue bottom) A2 = mon 12.03.2004 (dates, 10 not fat, white bottom) If in A3 I type: =" A1 " I would like a blue bottom, 12 fat, etc. and If I type =" A2 " a white bottom, 10 not fat, etc.... Is it possible? Thank you for any help. Thierry, Maybe you can use the Format Painter. Select the cell that has the formatting you wish to copy, and click the Format Painter. It's a paintbrush on a button on the Standard toolbar. Now click the c...

Outllook2007 to open my Outlook2002 Win7 says files locked.
I'm following 2002 to 2007 instructions step by step. When I point Outlook to my ..pst file, I get access restriction messages. How do I make sure these files can be accessed by Outlook 2007? Thanks. Which exact steps are these? From your Outlook 2002 machine, copy the pst-file to your Outlook 2007 machine and place it in a convenient location such as My Documents\Outlook\ In Outlook 2007 you can then reconnect to the pst-file via; File-> Open-> Outlook Data File... For details see; http://www.howto-outlook.com/howto/backupandrestore.htm -- Robert Sparnaai...

get an average of 3 cells, if they all have numbers, in excel.
In excel I need to average 3 cells, only if they all contain a number. and if they don't contain a number, don't display an error. "rudyjudy" <rudyjudy@discussions.microsoft.com> wrote: > In excel I need to average 3 cells, only if they all contain a number. > and if they don't contain a number, don't display an error. =IF(COUNT(A1,B2,C3)=3, AVERAGE(A1,B2,C3), "") Unfortunately, you will not see this response unless you use a different NG interface, e.g. Google Groups or a newsreader that access the MSnews server, msnews.microsof...

problem with index formula
The following fomula is contained in cell C16 and returns the correct values: =VLOOKUP(B6,'Active Clients'!$B$2:$K$954,10,FALSE) The following formula is contained in cell L18 and uses the result in C16 as a lookup value in the first "match": =INDEX(L21:M44,MATCH(C16,L21:L44,1),MATCH(M16,L21:M21,-1)) My promlem is that when I try to match on cell C16 my index formula returns an error. However, when I change the index formula to reference another cell (let's say cell D16) and I type in the value shown in cell C16, the index formula works fine. I think my problem has ...

excel locks up after selecting a cell #2
excel locks up after selecting a cell. When ever, I select a Cell, that will automatically selects all the cell and this freezes the entire computer. Can any body who would help me resolve this issue? Please help.... ...