|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 6th, 2005, 01:25 PM
|
|||
|
|||
Cell references change when entering new data
"X-No-Archive: yes"
In cell E1, I have the formula =COUNTIF($A1:$C20,$D$1)5 If I enter new data in A:C the cell references in the formula change. I will be autofilling the formula in column E, so the relative references $A1:$C20 will have to change. ie Cell E2 =COUNTIF($A2:$C21,$D$1)5 How do I use INDIRECT or an alternative function so that the cell references do not change when entering data. The simplest formula adaptation please, since I will be using the methodology in numerous complex/nested formulas. Thanks 
|
|
#2
May 6th, 2005, 07:38 PM
|
|||
|
|||
|
To lock A1:C20 using indirect you can use
=COUNTIF(INDIRECT("$A1:$C20"),$D$1)5 Regards, Peo Sjoblom " wrote: "X-No-Archive: yes" In cell E1, I have the formula =COUNTIF($A1:$C20,$D$1)5 If I enter new data in A:C the cell references in the formula change. I will be autofilling the formula in column E, so the relative references $A1:$C20 will have to change. ie Cell E2 =COUNTIF($A2:$C21,$D$1)5 How do I use INDIRECT or an alternative function so that the cell references do not change when entering data. The simplest formula adaptation please, since I will be using the methodology in numerous complex/nested formulas. Thanks
|
|
#3
May 6th, 2005, 07:48 PM
|
|||
|
|||
|
for E1, replace your $A1:$c20 reference with
OFFSET(D1,0,-3,20,3) & copy it down " wrote: "X-No-Archive: yes" In cell E1, I have the formula =COUNTIF($A1:$C20,$D$1)5 If I enter new data in A:C the cell references in the formula change. I will be autofilling the formula in column E, so the relative references $A1:$C20 will have to change. ie Cell E2 =COUNTIF($A2:$C21,$D$1)5 How do I use INDIRECT or an alternative function so that the cell references do not change when entering data. The simplest formula adaptation please, since I will be using the methodology in numerous complex/nested formulas. Thanks
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Syntax for inferred cell references | donesquire | Worksheet Functions | 4 | April 4th, 2005 09:29 PM |
| Format on data to import to Access tables? (I need your advice) | Niklas Östergren | General Discussion | 5 | December 13th, 2004 02:54 PM |
| GET.CELL | Biff | Worksheet Functions | 2 | November 24th, 2004 07:16 PM |
| SQL view of messed up action queries | Kendra | Running & Setting Up Queries | 2 | August 31st, 2004 09:53 PM |
| entering data changes the cell pattern | ozzie_90680 | Worksheet Functions | 2 | September 26th, 2003 09:07 PM |
Linear Mode
