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

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

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

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

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

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.

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
---

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
---

how to insert a collum on a sheet without it changing cell ref
 

welcome, glad to hear.
--
Max
Singapore

"Just Me" wrote in message
...
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