stop automatically changing formula!

i have a countif function
COUNTIF(Locking!I16:I40,"f")
when i copy this and paste it to the next cell, the formula automatically
change to COUNTIF(Locking!J16:J40,"f")

How do I stop it from changing column I to J?!?!?!
thanks.


0
caryn.tan (1)
6/2/2005 2:39:36 PM
excel 39879 articles. 2 followers. Follow

2 Replies
458 Views

Similar Articles

[PageSpeed] 34

Caryn,

=COUNTIF(Locking!$I$16:$I$40,"f")
or
=COUNTIF(Locking!$I16:$I40,"f")

HTH,
Bernie
MS Excel MVP


"caryn" <caryn.tan@gmail.com> wrote in message
news:d7n4u3$hgi$1@avnika.corp.mot.com...
> i have a countif function
> COUNTIF(Locking!I16:I40,"f")
> when i copy this and paste it to the next cell, the formula automatically
> change to COUNTIF(Locking!J16:J40,"f")
>
> How do I stop it from changing column I to J?!?!?!
> thanks.
>
>


0
Bernie
6/2/2005 3:26:43 PM
The $ signs in Bernie's example will lock the Row/Column references, and can
be used in any combination you like, locking down just Row or just Column or
both.

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
              Sys Spec - Win XP Pro /  XL 97/00/02/03

----------------------------------------------------------------------------
                  It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"caryn" <caryn.tan@gmail.com> wrote in message
news:d7n4u3$hgi$1@avnika.corp.mot.com...
> i have a countif function
> COUNTIF(Locking!I16:I40,"f")
> when i copy this and paste it to the next cell, the formula automatically
> change to COUNTIF(Locking!J16:J40,"f")
>
> How do I stop it from changing column I to J?!?!?!
> thanks.
>
>


0
ken.wright (2489)
6/2/2005 7:04:08 PM
Reply:

Similar Artilces:

changing the xsi:type
I have an arraylist with up to 4 different classes that can be contained. e.g.... <carpark> <car /> <truck /> <motorcycle /> <rv /> </carpark> It's largely working. The output is:- <carpark> <anytype xsi:type="xmlcar" /> <anytype xsi:type="xmltruck" /> <anytype xsi:type="xmlmotorcycle" /> <anytype xsi:type="xmlrv" /> </carpark> I've tried changing the element name, the arrayitem name, but I can't get it to rename from "xmlcar" to ...

change menu prompt dynamically
hi, I wanna change the menu prompt dynamically. how can I do that? regards. Monica.L. Hi Monica, What do mean by "menu prompt"? I'm pretty sure that a dozen of guys is ready to help ya, but you gotta be more specific ;-) Peter "monica" <mluchi@soft.fr.com> wrote in message news:1f2301c33ed5$751459d0$a601280a@phx.gbl... > hi, > > I wanna change the menu prompt dynamically. > how can I do that? > > regards. > Monica.L. If you are doing it dynamically, you need to look at CFrameWnd::OnMenuSelect (ie handle WM_MENUSELECT) and WM_SETMESSA...

How do I change the color for cell selection in excel?
I just started using Excel 2007. In the older versions when I select a row, column, or specific group of cells, Excel would highlight the group in a certain color. In Excel 2007, it simply surrounds the selected group of cells with a thick black line. I've tried going into Excel Options under Advanced but couldn't find how to do this. Can you assist me in changing this feature? On Dec 31 2007, 6:52 pm, dminliberty <dminlibe...@discussions.microsoft.com> wrote: > I just started using Excel 2007. In the older versions when I select a row, > column, or specific grou...

Excel Formula #3
Trying to write a formula that would say this: If there is an "x" in column F2 then add 1 to the number in column I2. In other words column I2 starts with a "0" and if there is a "x" placed in F2, then I want I2 to read "1". This is a win loss chart so there would be "x's" in other columns but I want it to keep adding "1" to I2. ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** A bit more clarity before suggesting a worksheet_...

change cell shading when a number differs from the previous number
I am generally familiar with Excel, but need some help on this particular problem. Is there a way to apply a format where: whenever the number in a column differs from the one previous to it, a cell shading color change is applied. For example, you have a column of numbers in Excel: 2 2 2 5 (cell shading color change applied) 5 5 5 2 (cell shading color change applied) 2 2 3 (cell shading color change applied) etc.... It doesn't matter what color is used, just so that it is applied whenever there is a change from the previous number. There has got to be an easy answer to this.... ...

change field number automatically when printing
We have a form in Excel, it is only one page, but we need a number on the right top corner of the sheet that changes automatically evey time we print the form Is there any way to do this Thank you You could use a Workbook BeforePrint macro that do that, assume the number is in cell G1 Range("G1").Value = Range("G1").Value + 1 like this Private Sub Workbook_BeforePrint(Cancel As Boolean) Range("G1").Value = Range("G1").Value + 1 End Sub -- Regards, Peo Sjoblom "sponder" <anonymous@discussions.microsoft.com> wrote in message news...

Data Validation drop down not showing and Formula Auditing bar greyed out
Good afternoon, I've created a spreadsheet that has multiple Validated cells. Each of these cells is validated using a list, with "In-cell Dropdown" Checked. However the drop down is not showing. The cell is still being validated as I cannot enter a value other than what is in the list. Also, on the formula auditing bar, the Trace Precedents, Remove Precedents, Trace Dependants, Remove Dependants, Remove all arrows and Trace Error buttons are all greyed out. I've checked that the sheets and workbook are not protected and as far as I can tell it has happened between file ...

How can I stop format change when copying data into unlocked cells
How can I stop the format changing when I copy data into an unlocked cell in a format protected worksheet? I need to allow people to both enter data or copy data from another source into cells so I have unlocked these cells but also don't want the format of the cells to be changed. If you enter data directly the format does not change but if you copy and paste data it changes the format. I know you can use "copy paste special values" but would like to know if there is some system way of doing this. Copy/paste this into the sheet module. Right-click on sheet ...

Protect but allow changes
Hi All, I would like to protect a worksheet but allow others to change column width and format numbers. Is that possible and how do I do this? Thank you for your help. Regards It depends on what version of excel you have, when you protect the sheet check allow format cells and format columns if you have this option -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 <anonymous@discussions.microsoft.com> wrote in message news:a2...

How do you calculate persentages and formulas
I am trying to calculate persentages on excel and i am also trying to work out formulas for different cells.Can anybody help. cheers F Calculate percentages by answering the question "this as compared to that": if "this" is in cell A1 and "that" is in cell B1, the percentage calculation is =A1/B1. As for "formulas for different cells", please give more information, and we'll be glad to help! We just need some more specifics to work with. ...

Can i use a formula in the excel chart wizard?
I am creating a coursework in which i have to analyse a lot of data in the spreadsheet. it is already cluttered with columns and i dont want to add extra columns to add to the clutter. i was wondering whether there is a way to plot absolute values of a column without changing the actual values in the column or creating a new column using ABS() function. Thanx You can't use formulas in the reference boxes in the chart wizard. You can only use range references or defined names. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ On 3...

How can I change the default paper size of a blank publication?
Publisher 97/98 asked for a choice to be made re paper size and measurements at setup. This allowed for European A4 to be the paper for the basic blank publication as opposed to US Letter. Is it possible to make this selection with PUblisher 2003. If so how? Do you have these settings available through your printer? If so set it up first, Publisher will follow along. You can change the page measurements to centimeters in Options, found under tools. -- Mary Sauer MS MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Paramount" <Paramoun...

Access 2007 Switchboard buttons stop working
Environments tested: Windows7, WindowsXP, Access 2007, Access 2003 Access data project (.adp) run in development mode in all tests. SQL Server 2005 back-end database. Trusted locations set up for all tests. The trusted location is the folder where the .adp resides, sub-folders included. Default file format: Access 2002-2003. I have a .adp application with a standard switchboard form as the main menu navigation. The buttons on the main switchboard form are used to launch forms, stored procedures, macros, and other child switchboard forms. The switchboard navigation buttons a...

Transfer HTML emails to computer automaticly?
How do you make Outlook automaticly move the "Inbox on MSN" e-mails into the regular Inbox, with the rest of my e-mails? You can't - you must move them manually. Rules do not work with MSN accounts. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, Dyan asked: | How do you make Outlook automaticly move the "Inbox on MSN" e-mails | into the regular Inbox, with the rest of my e-mails? ...

Nested Formula #2
In a table below, I am trying to "Count" the number of time a row begining with "H" has a number "1" iin it. I believe this has to be a "Nested" function but I am unsure. I will repeat this function for the leters A/U/S, and for the number 0/2 as well. H 1 1 0 2 0 H 2 2 1 0 0 A 1 1 1 1 1 U 0 0 U 2 1 1 S 0 H 1 2 1 2 1 A 2 2 2 1 1 U 1 1 1 1 2 Rick Hi Rick try the following for counting all H/1 in your range: =SUMPRODUCT((A1:A999="H")*(B1:H999=1)) assumption: Your range extends from A1...

I'm trying to change a clustered column to a stacked column
I click on clustered and the columns don't change to reflect the gradient What do you mean by "to reflect the gradient"? - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "petty" <petty@discussions.microsoft.com> wrote in message news:58AC32B4-0445-4231-8914-6C17784782D8@microsoft.com... >I click on clustered and the columns don't change to reflect the gradient ...

Big Problem for me! Formula to find matched numbers
Hi! I have a named cell -> combo_chk (cell J3) This combo_chk changes because of FOR..NEXT from code but the style i always like this : 4,5,12,40,45 I have 5 columns with values in the rows A B C D E 1 12 40 41 45 5 6 10 11 40 I want to find if the 1 on column A is including in the combo_chk then if the 12 on column B is including in the combo_chk e.t.c. If it is found return '1' otherwise return '0'. Im using the above formula in a code which it passes to the cell "=IF(ISERROR(FIND(C4,ComboChk)),0,1)" But the problem is that it isnt findind the exac...

How do I change the color of the Picture fill Speckles ?
I am making memorial folders and I like the picture fill (Speckles) background option. Can I change the color of the speckles and how? I have tried but I cannot figure this out. Thanks for any help, Laurie On Thu, 3 Nov 2005 17:30:10 +0000, lmp wrote (in article <185807CB-47F2-4C3E-B51D-E972501D49C5@microsoft.com>): > I am making memorial folders and I like the picture fill (Speckles) > background option. Can I change the color of the speckles and how? I have > tried but I cannot figure this out. Thanks for any help, Laurie You have answered your own question. It is a gif...

icon change in the task bar
I run Windows 7 and I have the Outlook program pinned to the task bar at the bottom of the screen. Up to a few days ago the icon for Outlook in the task bar was a yellow envelope. It has now mysteriously changed to what appears to be a generic program icon. How can I make it revert to its original icon. Thank you. You probably had a corruption in your icon cache from Windows. Unpin it and then pin it again from the Outlook shortcut that you can find in the Start Menu. -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com...

change source data in multiple charts
Hi I have over 30 charts where I need to change the source data and it's taking ages doing it manually, especially as each chart has 3 or 4 series. Is there an easy way of doing this, please? I've tried find and replace, but that doesn;t work Thanks We need a little more detail. 1. Are you modifying all the charts to use the same new range or does each chart use a different new range? Is the data compact, that is, if you choose the chart wizard and look at the Data Range tab does the Data Range box contain a simple reference like =Sheet1!$B$2:$E$7? Thanks, Shane "Tuxla&...

Pivot Table Error-"Microsoft cannont make changes because there are too many..."
I've encountered the following error when using the pivot table. Microsoft Excel cannot make this change because there are too many row or column items. Drag at least one row or column field off the Pivot Table, or to the page position. Alternatively, right click a field, and then click Hide or Hide Levels on the shortcut menu. In the layout tab of the pivot wizard I tried to select 9 rows, 2 columns, and 3 data. The first excel document that I pivoted had approximately 8800 lines (records) and the pivot table worked. I then tried to do the same layout criteria for another e...

Unable to change password on the next logon to OWA 2003
If I have created a user and place a check mark to change the password on the next logon, then it is not able to logon. If I don't check this option, everything works fine, including the option to change the password. The system is Windows 2003 SP1 + Exchange 2003 SP2 and all hotfixes associated. I have followed the KB 833734, but it did not work as expected. The user try logging on and a page 403 "You are not authorized to view this page" appears. Any Ideas? ...

how do I change the lerrers in the header of each cell to a name?
I am trying to create a spreadsheet that names or at least name the firstcell so that it will be present even when I scroll down . is this possible? I think you are talking about Freeze Panes. Select cell A2, assuming you have headers or whatever in A1, then click on Windows - Freeze Panes. Now scroll down and you will see that row 1 stays visible at all times. Is this what you want? HTH Otto "robush3" <robush3@discussions.microsoft.com> wrote in message news:8664E670-87FF-486A-8E8A-4B51002B5ACF@microsoft.com... >I am trying to create a spreadsheet that names o...

Exchange problems after domain name change
So I changed my domain name (from xxx.local to xxx.com, no not really xxx, but it's the same SLD, and the NETBIOS name didn't change). I started getting errors from the Exchange AL service about not being able to read something. I fixed that by resetting the domain controller on the recipient update services. But I'm still getting access denied errors elsewhere. For example, in the server properties (on both exchange servers in my domain), the log file location only displays "Access denied.Facility: Win32 ID no: 80070005 Exchange System Manager". I presume thi...

Formula Help... I'm really stuck here
I have this scenario: Product_ID Product_Price Starting_Week 154 10 1 6919 15 32 6919 16 34 154 12 33 6919 17 36 Given a ProductID and a Week I need to return the price for that Product_ID in that specific week or if the specific week doesn't exist I must return the price for the closest week. Per Examples: - If ProductID=6919 and Week= 33 I must get the price for the ProductID=6919 Week=32 (15) - If ...