|
| 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
February 17th, 2010, 02:59 AM
posted to microsoft.public.excel.newusers
|
|||
|
|||
how to insert a collum on a sheet without it changing cell referen
Its difficult to explain but I have this formula on a sheet:
=COUNTIF(Numbers!$A3:Numbers!AE3,1) I want to be able to insert a collum on the Numbers sheet and put in new data there without the cell reference $A3 changing to $B3 -- Thank You Much for taking your time to reply to my post 
|
|
#2
February 17th, 2010, 04:09 AM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
how to insert a collum on a sheet without it changing cell referen
I understand the problem. It only happens when you insert a column at column
A. If you insert a column to the right of A then it doesn't happen as you know. Is that an option? -- Russell Dawson Excel Student "Just Me" wrote: Its difficult to explain but I have this formula on a sheet: =COUNTIF(Numbers!$A3:Numbers!AE3,1) I want to be able to insert a collum on the Numbers sheet and put in new data there without the cell reference $A3 changing to $B3 -- Thank You Much for taking your time to reply to my post
|
|
#3
February 17th, 2010, 04:40 AM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
how to insert a collum on a sheet without it changing cell ref
I would like to insert the collumn at the first collumn, the data I insert
has a date then other numbers below that, I need to grab the date for 1 calculation then the numbers for others. I would have a simular problem with the date if it is not in A1 -- Thank You alot for taking your time to reply to my post "Russell Dawson" wrote: I understand the problem. It only happens when you insert a column at column A. If you insert a column to the right of A then it doesn't happen as you know. Is that an option? -- Russell Dawson Excel Student "Just Me" wrote: Its difficult to explain but I have this formula on a sheet: =COUNTIF(Numbers!$A3:Numbers!AE3,1) I want to be able to insert a collum on the Numbers sheet and put in new data there without the cell reference $A3 changing to $B3 -- Thank You Much for taking your time to reply to my post
|
|
#4
February 17th, 2010, 01:06 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
how to insert a collum on a sheet without it changing cell ref
INDIRECT will allow you to always point to the fixed range impervious to
future col insertions/deletions: =COUNTIF(INDIRECT("'Numbers'!A3:AE3"),1) The range string: 'Numbers'!A3:AE3 within INDIRECT is just a text string, so you don't need to use any $ signs. Any worth? hit the YES below. -- Max Singapore --- "Just Me" wrote: I would like to insert the collumn at the first collumn, the data I insert has a date then other numbers below that, I need to grab the date for 1 calculation then the numbers for others. I would have a simular problem with the date if it is not in A1
|
|
#5
February 17th, 2010, 01:15 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
how to insert a collum on a sheet without it changing cell ref
OK i will experiment with:
=COUNTIF(INDIRECT("'Numbers'!A3":AE3),1) because the :AE3 part of the range needs to change as I populate the Numbers sheet. -- Thank You all Much for taking your time to reply to my post "Max" wrote: INDIRECT will allow you to always point to the fixed range impervious to future col insertions/deletions: =COUNTIF(INDIRECT("'Numbers'!A3:AE3"),1) The range string: 'Numbers'!A3:AE3 within INDIRECT is just a text string, so you don't need to use any $ signs. Any worth? hit the YES below. -- Max Singapore --- "Just Me" wrote: I would like to insert the collumn at the first collumn, the data I insert has a date then other numbers below that, I need to grab the date for 1 calculation then the numbers for others. I would have a simular problem with the date if it is not in A1
|
|
#6
February 17th, 2010, 01:25 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
how to insert a collum on a sheet without it changing cell ref
If that's the case, maybe just grab the entire row 3 then?
=COUNTIF(INDIRECT("'Numbers'!3:3"),1) -- Max Singapore --- "Just Me" wrote: OK i will experiment with: =COUNTIF(INDIRECT("'Numbers'!A3":AE3),1) because the :AE3 part of the range needs to change as I populate the Numbers sheet.
|
|
#7
February 17th, 2010, 01:39 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
how to insert a collum on a sheet without it changing cell ref
Sorry, I got carried away earlier. If grabbing the entire row 3, no need for
INDIRECT, just: =COUNTIF(Numbers!3:3,1) -- Max Singapore ---
|
|
#8
February 18th, 2010, 01:23 AM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
how to insert a collum on a sheet without it changing cell ref
I would like to thank you both for helping me with my problem, I learned i
could insert the second column then manipulate the numbers and the page would work without changing the cell addresses. But the real trick was to only call for the row number range and not the collum at all so the collum would not update: =COUNTIF(Numbers!3:3,1) and the place where I was calling the date just changed to: =INDIRECT("'Numbers'!A1") that way the cell reference does not change when I insert a collumn on the numbers sheet and will show the proper date once the data has been pasted onto the cells. -- Thank You both very Much for taking your time to reply to my post I learned alo alo alot "Max" wrote: Sorry, I got carried away earlier. If grabbing the entire row 3, no need for INDIRECT, just: =COUNTIF(Numbers!3:3,1) -- Max Singapore ---
|
|
#9
February 18th, 2010, 02:10 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
how to insert a collum on a sheet without it changing cell ref
welcome, glad to hear.
-- Max Singapore "Just Me" wrote in message news  I would like to thank you both for helping me with my problem, I learned i could insert the second column then manipulate the numbers and the page would work without changing the cell addresses. But the real trick was to only call for the row number range and not the collum at all so the collum would not update: =COUNTIF(Numbers!3:3,1) and the place where I was calling the date just changed to: =INDIRECT("'Numbers'!A1") that way the cell reference does not change when I insert a collumn on the numbers sheet and will show the proper date once the data has been pasted onto the cells. -- Thank You both very Much for taking your time to reply to my post I learned alo alo alot
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
