| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula that does not change the cell range
I'm just trying to COPY/PASTE a formula that will use the same range of cells
but I'm having problem finding out how ( I know it changes "relativately" accordinly to the rows and columns). I want it to stay UNCHANGE! Tx. Excel 2003/ Windows XP. Also Q2 in the same formula ( COUNTIF+COUNTIF+COUNTIF+...) is there any way that I could change the criteria just ONCE and it will change in the whole formula. Ex. =COUNTIF(SHEET1!L1:L300,"SUNDAY")+COUNTIF(SHEET2!L 1:L300,"SUNDAY")+etc... I'like to COPY/PASTE always in L and change just one time SUNDAY for MONDAY(after pasting it ) resulting in MONDAY in all the countif ( about 12 ). Tx |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula that does not change the cell range
hi,
Q1. Don't use relative reference ie A1. use absolute reference ie $A$1 Q2. don't put sunday in the formula. put it in a cell like A1. Put the cell reference in the formula then change the cell contents as/when needed. not =COUNTIF(SHEET1!L1:L300,"SUNDAY")+... but =COUNTIF(sheet1!L1:L300,A1)+... regards FSt1 "FC" wrote: I'm just trying to COPY/PASTE a formula that will use the same range of cells but I'm having problem finding out how ( I know it changes "relativately" accordinly to the rows and columns). I want it to stay UNCHANGE! Tx. Excel 2003/ Windows XP. Also Q2 in the same formula ( COUNTIF+COUNTIF+COUNTIF+...) is there any way that I could change the criteria just ONCE and it will change in the whole formula. Ex. =COUNTIF(SHEET1!L1:L300,"SUNDAY")+COUNTIF(SHEET2!L 1:L300,"SUNDAY")+etc... I'like to COPY/PASTE always in L and change just one time SUNDAY for MONDAY(after pasting it ) resulting in MONDAY in all the countif ( about 12 ). Tx |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula that does not change the cell range
Thanks. How can I change to absolute reference?( describe it for me please)
(that will be the question) as Q1. In Q2 I cannot use your sugestion because from L1 to L300 the cells are changing with the 7 days of the weeks in all the sheets in column L,that's why I need to change it each time in order to count how many Sundays, Mondays... I have. "FSt1" wrote: hi, Q1. Don't use relative reference ie A1. use absolute reference ie $A$1 Q2. don't put sunday in the formula. put it in a cell like A1. Put the cell reference in the formula then change the cell contents as/when needed. not =COUNTIF(SHEET1!L1:L300,"SUNDAY")+... but =COUNTIF(sheet1!L1:L300,A1)+... regards FSt1 "FC" wrote: I'm just trying to COPY/PASTE a formula that will use the same range of cells but I'm having problem finding out how ( I know it changes "relativately" accordinly to the rows and columns). I want it to stay UNCHANGE! Tx. Excel 2003/ Windows XP. Also Q2 in the same formula ( COUNTIF+COUNTIF+COUNTIF+...) is there any way that I could change the criteria just ONCE and it will change in the whole formula. Ex. =COUNTIF(SHEET1!L1:L300,"SUNDAY")+COUNTIF(SHEET2!L 1:L300,"SUNDAY")+etc... I'like to COPY/PASTE always in L and change just one time SUNDAY for MONDAY(after pasting it ) resulting in MONDAY in all the countif ( about 12 ). Tx |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula that does not change the cell range
As FSt1 told you, an absolute address has $ signs in it, as in $A$1. For more
information, look up absolute addresses in Help. -- Regards, Fred "FC" wrote in message ... Thanks. How can I change to absolute reference?( describe it for me please) (that will be the question) as Q1. In Q2 I cannot use your sugestion because from L1 to L300 the cells are changing with the 7 days of the weeks in all the sheets in column L,that's why I need to change it each time in order to count how many Sundays, Mondays... I have. "FSt1" wrote: hi, Q1. Don't use relative reference ie A1. use absolute reference ie $A$1 Q2. don't put sunday in the formula. put it in a cell like A1. Put the cell reference in the formula then change the cell contents as/when needed. not =COUNTIF(SHEET1!L1:L300,"SUNDAY")+... but =COUNTIF(sheet1!L1:L300,A1)+... regards FSt1 "FC" wrote: I'm just trying to COPY/PASTE a formula that will use the same range of cells but I'm having problem finding out how ( I know it changes "relativately" accordinly to the rows and columns). I want it to stay UNCHANGE! Tx. Excel 2003/ Windows XP. Also Q2 in the same formula ( COUNTIF+COUNTIF+COUNTIF+...) is there any way that I could change the criteria just ONCE and it will change in the whole formula. Ex. =COUNTIF(SHEET1!L1:L300,"SUNDAY")+COUNTIF(SHEET2!L 1:L300,"SUNDAY")+etc... I'like to COPY/PASTE always in L and change just one time SUNDAY for MONDAY(after pasting it ) resulting in MONDAY in all the countif ( about 12 ). Tx |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula that does not change the cell range
Sorry. But my question is not even close to be answer, there is not addess
anywhere on my question? anyways thank you for trying both of you. Signing out... "Fred Smith" wrote: As FSt1 told you, an absolute address has $ signs in it, as in $A$1. For more information, look up absolute addresses in Help. -- Regards, Fred "FC" wrote in message ... Thanks. How can I change to absolute reference?( describe it for me please) (that will be the question) as Q1. In Q2 I cannot use your sugestion because from L1 to L300 the cells are changing with the 7 days of the weeks in all the sheets in column L,that's why I need to change it each time in order to count how many Sundays, Mondays... I have. "FSt1" wrote: hi, Q1. Don't use relative reference ie A1. use absolute reference ie $A$1 Q2. don't put sunday in the formula. put it in a cell like A1. Put the cell reference in the formula then change the cell contents as/when needed. not =COUNTIF(SHEET1!L1:L300,"SUNDAY")+... but =COUNTIF(sheet1!L1:L300,A1)+... regards FSt1 "FC" wrote: I'm just trying to COPY/PASTE a formula that will use the same range of cells but I'm having problem finding out how ( I know it changes "relativately" accordinly to the rows and columns). I want it to stay UNCHANGE! Tx. Excel 2003/ Windows XP. Also Q2 in the same formula ( COUNTIF+COUNTIF+COUNTIF+...) is there any way that I could change the criteria just ONCE and it will change in the whole formula. Ex. =COUNTIF(SHEET1!L1:L300,"SUNDAY")+COUNTIF(SHEET2!L 1:L300,"SUNDAY")+etc... I'like to COPY/PASTE always in L and change just one time SUNDAY for MONDAY(after pasting it ) resulting in MONDAY in all the countif ( about 12 ). Tx |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula that does not change the cell range
I'm sorry to have interrupted you. I completely misunderstood. I thought you
were looking for a solution to your problem. But you just want to complain. So I'll get out of the way so you can do so in peace. -- Regards, Fred "FC" wrote in message ... Sorry. But my question is not even close to be answer, there is not addess anywhere on my question? anyways thank you for trying both of you. Signing out... "Fred Smith" wrote: As FSt1 told you, an absolute address has $ signs in it, as in $A$1. For more information, look up absolute addresses in Help. -- Regards, Fred "FC" wrote in message ... Thanks. How can I change to absolute reference?( describe it for me please) (that will be the question) as Q1. In Q2 I cannot use your sugestion because from L1 to L300 the cells are changing with the 7 days of the weeks in all the sheets in column L,that's why I need to change it each time in order to count how many Sundays, Mondays... I have. "FSt1" wrote: hi, Q1. Don't use relative reference ie A1. use absolute reference ie $A$1 Q2. don't put sunday in the formula. put it in a cell like A1. Put the cell reference in the formula then change the cell contents as/when needed. not =COUNTIF(SHEET1!L1:L300,"SUNDAY")+... but =COUNTIF(sheet1!L1:L300,A1)+... regards FSt1 "FC" wrote: I'm just trying to COPY/PASTE a formula that will use the same range of cells but I'm having problem finding out how ( I know it changes "relativately" accordinly to the rows and columns). I want it to stay UNCHANGE! Tx. Excel 2003/ Windows XP. Also Q2 in the same formula ( COUNTIF+COUNTIF+COUNTIF+...) is there any way that I could change the criteria just ONCE and it will change in the whole formula. Ex. =COUNTIF(SHEET1!L1:L300,"SUNDAY")+COUNTIF(SHEET2!L 1:L300,"SUNDAY")+etc... I'like to COPY/PASTE always in L and change just one time SUNDAY for MONDAY(after pasting it ) resulting in MONDAY in all the countif ( about 12 ). Tx |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I change cell color based upon data range within the cell? | Excel Worksheet Functions | |||
| How do I change a cell range with a reference cell? | Excel Discussion (Misc queries) | |||
| Look for change next blank cell in Range | Excel Worksheet Functions | |||
| how do I make a formula NOT change when the data range is moved? | Excel Discussion (Misc queries) | |||
| HOW TO USE A FORMULA TO CHANGE CELL COLOR ACCORDING TO DATE RANGE. | Excel Worksheet Functions | |||
Linear Mode
