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

  • Thread starter Thread starter Just Me
  • Start date Start date
J

Just Me

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
 
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
 
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.
 
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.
 
If that's the case, maybe just grab the entire row 3 then?
=COUNTIF(INDIRECT("'Numbers'!3:3"),1)
 
Sorry, I got carried away earlier. If grabbing the entire row 3, no need for
INDIRECT, just: =COUNTIF(Numbers!3:3,1)
 
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.
 
Back
Top